The life and times of this developer...

WPF, C#, XML and other stuff

  Home  |   Contact  |   Syndication    |   Login
  24 Posts | 0 Stories | 28 Comments | 0 Trackbacks

News

Tag Cloud


Archives

Post Categories

Cool Tools

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]

 #

 

posted on Monday, February 21, 2011 9:54 AM

Feedback

# re: T-SQL select where and group by date 2/21/2011 5:56 PM MD Aneesuddin Arif
Thank you for posting .Helped me a lot.

# re: T-SQL select where and group by date 11/26/2013 2:56 AM chaiwuty
Thank you very much.

Post A Comment
Title:
Name:
Email:
Comment:
Verification: