This weeks tip:
The DATEDIFF function is very simple and very powerful…
With it you can:
- Count the days, weeks, years, since an event occurred (I’ve done this to impress my kids by telling them how many day old they are)
Select DATEDIFF(D, ‘1/1’11’, GETDATE()) –How many days since New Years Day 2011
- Count the minutes, seconds, milliseconds, microseconds, nanoseconds until something occurs (I’ve counted down until Friday at 5 PM a few times as well as to the day I retire).
Select DATEDIFF(D, GETDATE(), ‘1/1’15’) –How many days until the Coach Kill leads the Gopher Nation to the Rose Bowl
- You can also get creative with it, by using some simple multiplication, you can count weekdays like Jeff Smith did here – How To Calculate the Number of Week Days Between two Dates.
-
You can round date as explained here: SQL Server datetime rounding made easy
-
You can remove the time portion of a datetime as explained here: Removing Time From A Datetime Value
- A whole slew of basic tricks here: Yet another date FAQ, part I: simple tips & tricks
Previous Tips O’ the Week
- TSQL Tip O’ the Week – 8/9/11 – DDL Triggers for Extra Protection
- TSQL Tip O’ the Week – 8/2/11 – Records Per Day
- TSQL Tip O’ the Week – 7/26/11 – A Column to CSV
- The Entire List…
