Monday, March 10, 2014

Cool trick to remove times from datetime data types in MSSQL

​A frequent request in SQL is to roll up a bunch of records by date.  Often when you dig into the data you find that the column in question has that snarky time added to the end of all of the dates and nothing will roll up.  So what do you do?
A common approach is to pull out the CONVERT funtion and start treating your dates as strings and go from there.  Unfortunately, that's not really efficient.
I ran across this little gem:
SELECT DATEADD(dd, DATEDIFF(dd, 0, dbo.MYDATETIME), 0) AS MYDATETIME
What this does is take your date and figure out how many days are in it from your base date (usually 1/1/1900) and then add that many days back to your base date.  The time value is stripped off because you're working in days.
Date math in sql works with integers and is very fast.  Give it a try the next time you need to strip off that time.