T-SQL select where and group by date
- by bconlon
T-SQL has never been my favorite language, but I need to use it on a fairly regular basis and every time I seem to Google the same things. So if I add it here, it might help others with the same issues, but it will also save me time later as I will know where to look for the answers!!
1. How do I SELECT FROM WHERE to filter on a DateTime column?
As it happens this is easy but I always forget. You just put the DATE value in single quotes and in standard format:
SELECT StartDate FROM Customer WHERE StartDate >= '2011-01-01' ORDER BY StartDate
2. How do I then GROUP BY and get a count by StartDate?
Bit trickier, but you can use the built in DATEADD and DATEDIFF to set the TIME part to midnight, allowing the GROUP BY to have a consistent value to work on:
SELECT DATEADD (d, DATEDIFF(d, 0, StartDate),0) [Customer Creation Date], COUNT(*) [Number Of New Customers]
FROM Customer
WHERE StartDate >= '2011-01-01'
GROUP BY DATEADD(d, DATEDIFF(d, 0, StartDate),0)
ORDER BY [Customer Creation Date]
Note: [Customer Creation Date] and [Number Of New Customers] column alias just provide more readable column headers.
3. Finally, how can you format the DATETIME to only show the DATE part (after all the TIME part is now always midnight)?
The built in CONVERT function allows you to convert the DATETIME to a CHAR array using a specific format. The format is a bit arbitrary and needs looking up, but 101 is the U.S. standard mm/dd/yyyy, and 103 is the U.K. standard dd/mm/yyyy.
SELECT CONVERT(CHAR(10), DATEADD(d, DATEDIFF(d, 0, StartDate),0), 103) [Customer Creation Date], COUNT(*) [Number Of New Customers]
FROM Customer
WHERE StartDate >= '2011-01-01'
GROUP BY DATEADD(d, DATEDIFF(d, 0, StartDate),0)
ORDER BY [Customer Creation Date]
#
