Date and Time Calculations for Prior Day Bracketing
Reduce, Reuse, Recycle
I’ve been touting lately in presentations at various SQL events that if you’re going to do something more than once to take time to script it out and save it for later. I’ve been doing that using native Templates in Microsoft SQL Server Management Studio for years now. Lately, however, I’ve also been employing Snippets available in Red Gate’s SQLPrompt product. As I work on my collection of recyclable code for a book I am mulling-over I will be posting out some of those templates & snippets that fit into my #SQLHacker initiative I blogged about here. This is one of those cases where this code is something I see people asking about frequently enough to release as a post here at The SQL Agent Man.
Bracketing Dates
Developers and Database Administrators alike are constantly dealing with queries that have to be date-bound in some fashion. I’m referring to start date / end date scenarios. I touched upon this topic from a different angle back in 2009 when I authored an article for MSSQLTips on the topic of finding a specific day of the week forward or backward in time (for instance the last Tuesday or five Mondays from now.) This time around I want to provide a quick script for determining a very common date range bracket when dealing with daily reporting.
Daily Reports Bracketing Functions for Start Date
Most of the daily reports I encounter encompass the time frame of Midnight yesterday until some terminus just shy of Midnight today. Below is the calculation I use to determine the start date of any daily report I build:
-- To Get Midnight Yesterday SELECT DATEADD(d, -1, DATEDIFF(d, 0, GETDATE()))
----------------------- 2013-04-17 00:00:00.000
Daily Reports Bracketing Functions for End Date
Author’s Note: Aaron Betrand raised a very valid point in the comments below and I’ve since come back here to refine the code I orginally presented.
Typically you’re going to bracket your daily reporting on the start and end of the previous day. I’ve already shown you how to calculate the start predicate. Now for end predicate. Here the preference would be to simply use the same coding construct as presented above for start date but use midnight of the current day and then query for the records >= start time and < end time where that end time is midnight today:
>-- To Get Midnight Today SELECT DATEADD(d, -0, DATEDIFF(d, 0, GETDATE()))
----------------------- 2013-04-18 00:00:00.000
You end up with the following as your base filtering clause as a result:
WHERE some_column >= DATEADD(d, -1, DATEDIFF(d, 0, GETDATE()))
AND some_colum < DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()))
What About Other Terminal Dates?
You can use similar code to get time slices for reports by tweaking the offset in the DATEADD function. For example you may wish to pull in records for Midnight yesterday until noon yesterday. Add an offset to the existing base code construct and it’s very easy to account for this option:
--Return Value for Noon Yesterday SELECT DATEADD(hh, 12, DATEADD(d, -1, DATEDIFF(d, 0, GETDATE())))
----------------------- 2013-04-17 12:00:00.000
#SQLHacker Tips
Since this is one of my #SQLHacker posts we got down and dirty quick. Stay tuned here for more quick-hit tips for making your SQLlife easier. Also, if you’re a blogger, tag your posts with #SQLHacker and join the effort to provide easy, quick, and free solutions for today’s SQL Professional.
Do you want to get more than just a quick hit of SQL Server? How about a week of SQL Server training with a side of fun, networking, and rejuvenation? Then join Tim Ford, Aaron Bertrand, Buck Woody and others in Alaska this May for SQL Cruise Alaska 2013! Warm weather more your style? Then how about SQL Cruise Caribbean 2014 with Tim Ford, Andrew Kelly, Neil Hambly (and more SQL luminaries to be announced soon). See sqlcruise.com for details.
Second-based granularity isn’t enough either. Let’s say you choose 23:59:59 as the “end” of the day. Now a conversion or a schema change makes the underlying data type SMALLDATETIME. Guess what? Your 23:59:59 just rounded up to the next day, and you don’t get any errors, you just include more rows than you thought. Underlying data type is datetime or datetime2, and you could theoretically miss data from the last second of the day. And finally, conversion leads to DATE, and 23:59:59 doesn’t round up, it gets truncated. If ultimately you are checking against a column that includes time, your query will only include rows posted exactly at midnight.
It’s impossible to pick a magic “end of the day” because each data type is going to handle it differently.
Bracketing a day (or any range) when you want full days should always be an open-ended range. >= start date and < DATEADD(DAY, 1, end date). While you can use BETWEEN for DATE data types it's still better IMHO to be consistent.
Yes Aaron, I agree that schema always plays a role in the coding and things may break should schema changes occur. (SELECT * anyone?) However, when you’re dealing with a situation where the schema is stable, and (for instance) you’re pulling an extract that is always based upon Midnight yesterday to 11:59:59 and dealing with a schema that accommodates this then it works just fine. Of course the caveat is implied that anything you read on the Internet that pertains to coding should always be tested to fit your independent and variable needs in a non-production environment.
Date and Time Calculations for Prior Day Bracketing: http://t.co/fPpQ3kWgfV