By: Aaron Bertrand
In this video tutorial we will look at using SQL Server date and time shorthand and some of the confusion this can cause with date manipulation when using these shorthand codes.
Referenced Links in Video
- SQL Server Dates Tips (bit.ly/mssql-dates)
- Bad Habits Revival (bit.ly/sql-bad-habits)
- Using T-SQL to find events that overlap (or don't) in SQL Server (bit.ly/overlapping-ranges)
Recommended Reading
- Date and Time Conversions Using SQL Server
- Format SQL Server Dates with FORMAT Function
- Add and Subtract Dates using DATEADD in SQL Server
- SQL Server Date and Time Functions with Examples
T-SQL Script
The below script will allow you to run the tests discussed in the video. You can also download the script.
---------------------------------------------------------------
-- date part abbreviations for Saturday, July 4th, 2020
---------------------------------------------------------------
SELECT [DATEPART: W ] = DATEPART( W , '20200704' ), -- (a) 27 (b) 26 (c) 7
[DATEPART: Y ] = DATEPART( Y , '20200704' ); -- (a) 2020 (b) 20 (c) 186
---------------------------------------------------------------
-- date part *without* abbreviations for Saturday, July 4th, 2020
---------------------------------------------------------------
SELECT [DATEPART: WEEK ] = DATEPART( WEEK , '20200704' ),
[DATEPART: YEAR ] = DATEPART( YEAR , '20200704' );
---------------------------------------------------------------
-- what happens with sentinel / integer math
-- when column/param changes to new types?
---------------------------------------------------------------
DECLARE @d date = 0;
SELECT @d;
GO
DECLARE @d datetime2 = GETDATE();
SELECT @d + 1;
GO
---------------------------------------------------------------
-- cryptic tricks for today:
---------------------------------------------------------------
SELECT [Today1] = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0),
[Today2] = DATEADD(DAY, DATEDIFF(DAY, '20000101', GETDATE()), '20000101'),
[Today3] = CONVERT(datetime, CONVERT(char(8), GETDATE(), 112)),
[Today4] = CONVERT(datetime, FORMAT(GETDATE(), 'yyyyMMdd')),
---------------------------------------------------------------
-- or beginning of this month:
---------------------------------------------------------------
[FirstOfThisMonth1] = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
[FirstOfThisMonth2] = DATEADD(DAY, 1 - DATEPART(DAY, GETDATE()), GETDATE());
GO
---------------------------------------------------------------
-- better:
---------------------------------------------------------------
DECLARE @d date = GETDATE();
SELECT [Today] = @d,
[FirstOfThisMonth] = DATEFROMPARTS(YEAR(@d), MONTH(@d), 1);
---------------------------------------------------------------
-- cryptic work for overlapping windows
---------------------------------------------------------------
CREATE TABLE #DeployFreezes
(
FreezeID int,
EventStart date,
EventEnd date
);
INSERT #DeployFreezes(FreezeID, EventStart, EventEnd)
VALUES(1, '20200428', '20200501'), -- outside our sprint, May 5 - 19
(2, '20200503', '20200506'), -- partial overlap at beginning
(3, '20200501', '20200531'), -- complete overlap
(4, '20200507', '20200512'), -- complete containment
(5, '20200518', '20200520'), -- partial overlap at end
(6, '20200521', '20200525'); -- outside our sprint
GO
DECLARE @SprintStart date = '20200505', @SprintEnd date = '20200519';
SELECT FreezeID, EventStart, EventEnd
FROM #DeployFreezes
WHERE (@SprintStart < EventStart AND @SprintEnd >= EventStart) -- partial overlap at beginning
OR (@SprintStart >= EventStart AND @SprintEnd <= EventEnd) -- complete overlap
OR (@SprintStart > EventStart AND @SprintEnd < EventEnd) -- complete containment
OR (@SprintStart <= EventEnd AND @SprintEnd > EventEnd) -- partial overlap at end
SELECT FreezeID, EventStart, EventEnd
FROM #DeployFreezes
WHERE (@SprintEnd >= EventStart AND @SprintStart <= EventEnd) -- covers all four scenarios
GO
DROP TABLE #DeployFreezes;
Video Transcript
Hi, I'm Aaron Bertrand. With a series of short videos for mssqltips.com. Today I wanted to talk about datetime shorthand, and other datetime tricks that people use, that can lead them astray.
There are several pieces of shorthand that people can use with datetime, data types that can cause problems. So one is datepart abbreviations. So instead of typing out the word month or the word day, people type in shorthand like MM or DD, that kind of thing. There are Sentinel values. People who will use a datetime of zero to represent 1900-01-01. This can lead to issues as well. And then there's integer math. So people rely on the fact that datetime and smalldatetime, allow you to add or subtract, integer values to add days or subtract days from the datetime.
Then we have some tricks that cause confusion, and can harm readability. One of those is using DATEADD or DATEDIFF, or both of them combined, to get the beginning of a period or to strip the time from a date time value. And then there's also an issue where people use Verbose code to find overlaps, between date ranges. So let's take a look at a couple of demos.
So here's one that uses date part abbreviations. So let's say we have a date like Saturday, July 4th, 2020. I want you to write down on your notepad, or just keep in your head, the answer that you think that these two queries will return. The first one is datepart W of July 4th, 2020. Is that going to return the 27th week? The 26th week? Or the seventh week? And then datepart Y, is that going to return 2020? Just 20, keep in mind it's only one Y, not two Ys or four Ys, or 186 as the answer. So I'll give you a second, to write down what you think your answers will be, out of these. And then we'll run it and we'll talk about why. So datepart W of July 4th, 2020, and datepart Y of July 4th, 2020. So datepart W is seven. And you might think, well, that's definitely not the seventh week of 2020, so what is going on here. W in this case stands for day of week, not the week. And then datepart Y is 186. That's not the year, that's not a shorthand for the year, that's not the number of years since some base day. That is the day of the year. Y stands for day of year, not year. So that throws a lot of people off.
So if we scroll down, we can see that without the abbreviations, we still have July 4th of 2020. And when we actually type out week and type out year, then not only are we going to get the right answer, but it's also much clearer to the reader, or the maintainer of this code that you've written, what your intention was. So in this case, we get 27 for the 27th week, and 2020 for the year. Which is what we expect. So don't use shorthand for these datetime dateparts. And then what about Sentinel and Integer math? So, when I have a value like this, and I say declare @d datetime is equal to zero, that is telling SQL server that we actually want, zero days after 1900-01-01. so January 1st, 1900, and add zero days. And you can see that with the results, we get 1900-01-01. And we also have this other scenario, where people use this shorthand, to add or subtract dates from a datetime variable. So we have declare @d datetime is equal to gatedate. And then we say select @d plus one. Well this takes getdate and adds 24 hours to it. It adds one day to that variable. And this works, no problem. And this is being recorded on April 27th. So this is returning April 28th, at 5:53pm.
Now, what happens if your variables, or your columns, or your parameters, to your stored procedures or functions, or the return data type, any of these things changes from the old types, datetime or smalldatetime, to the new types? Like datetime2. In both of these cases, we're going to get an error message. Operand type clash int is incompatible with datetime2. Same thing happens with date. The new types have no idea, what the Sentinel date of zero is. int is incompatible date. Same thing, if we try to add or subtract. We get these error messages. So, this can happen when you change data types, somewhere completely separate from the code that is accessing those data types or parameters. So, now go and try and find all of the instances, throughout your application code, where you might be adding or subtracting from, a datetime variable, or column, or parameter. That is going to be very hard to find those. And so I strongly recommend using dateadd in these cases. Even if it's just adding a day or subtracting a day. Just use the explicit dateadd. Yes, it's eight extra characters of typing or whatever it might be, but it's totally worth it.
We have a bunch of cryptic tricks, for getting things like today without a time, or the beginning of this month. So I'm not going to go through all of these, and explain what they all are. This scripts will be downloadable below this video. But these are tricks that I see all kinds of people use, and some of these use some of the other shorthand I'm talking about too. So this DATEADD DAY, DATEDIFF DAY, zero GETDATE zero, relies on the fact that this is actually 1900-01-01. So that's add two 1900-01-01, the difference between 1900-01-01 and today. So add that many days to zero, and you will get today at midnight. So this is just a way, to supplement the fact that, there isn't a proper getdate in SQL Server. getdate is actually getdatetime. Because it includes the timestamp as well.
We should have a function that says, just get me the date, I don't care about the time. And I can show you how we actually can do that. But there are other ways to do this too. You can use some specific base date. So this is a little more self documenting, but it's still a lot of cumbersome code. And then you could also convert it to a string, to trim the time and then convert it back to a datetime. Don't do this, this is inefficient, format is even less efficient. This is actually twice as expensive, as converting to a string and back again. These are ways you can definitely get the result you want, but not in the way that you should want to get them.
Similar tricks exists for the beginning of this month. So these are two of the most common ones that I see. Add the number of months that have passed since 1900-01-01. Add that many months to 1900-01-01 is cryptic; it works, but it's very cryptic. And then also, you can subtract from today. So take the datepart of today, today is the 27th. I want to subtract 26 days from today, and then convert that to a date. That will give you today at midnight as well. So let's just take a look at those.
So these are all going to return either, the first four are going to return today, the second two are going to return the first of the month. One of them includes a timestamp and one of them doesn't. So these are the four that return today. These all work. But isn't there a better way to do this? And yes, of course there is. So declare @d date is equal to getdate. That automatically truncates the time, and it converts getdate into just a date. You could also use sysdatetime, sysutcdatetime, if you want to be using the more modern versions of these functions, that's fine too. And then once you have that, once you have today as a date, then you can very easily, and whether it includes the day, the time or not, you can get the first of this month, by using datefromparts year @d month @d one. So that is much less cryptic than those other approaches.
We talked about overlapping windows. So let's say we have a deployment freezes table, and this has a bunch of deployment freezes that are in place. There's probably other metadata that's missing here, like the department and reasons and that kind of thing. But let's just say we have a bunch of freezes, for our organization, where we can't deploy new versions of code. And we have a sprint that's coming up from May 5th to May 19th. That's our next sprint. And we need to plan our development, and when we're going to deploy changes. So we need to take into account all of these deployment freezes that are happening, from today on. Now, what I want to do is, I want to return the rows from this table, that do overlap with our sprint that's coming up. So I want to make sure I know about all the deployment freezes, that are going to affect our sprint, and how we can determine when we could deploy during our sprint.
So we have a sprint that's May 5th to May 19th. And what I see people do, is they come up with all these convoluted conditions, that can happen for two date ranges to overlap. You can have a partial overlap at the beginning of the range, you could have a complete overlap, you could have complete containment. So both the start and the end of the thing you care about, is in between the start and the end of the event. And then you could also have a partial overlap at the end. So you get these these big long crazy or and, and or conditional where clauses, that are very hard to read and interpret. And then if you want to leave one end of the range open, so you just want to make sure, you capture anything that runs into your sprint. And you don't care if it runs after or not. Then you have to figure out which of these can I leave out. There's a much simpler way to do that. And that is to just say, where the end of the event you care about, starts on or after the start of your range. And the start of the event you care about, starts before the end of your range. So these two sets of queries will get the same answers, but which one is easier to read? So if we take a look at this, I didn't run any of this of course.
So we're going to create our table, we're going to insert our six rows. And now what we should get out of this table, is when we run this query, we should get the four rows in the middle. So we should get rows two, three, four and five, and we should leave out rows one and six. Because those two are outside of our sprint. We only care about the deployment freezes, that are going to impact our our sprint directly. So let's run these two, insert six rows. And then we can run our two queries. And we'll see that those return the exact same results. So both return row freezes, two, three, four and five. So just a much simpler way to find these overlapping ranges. I'm going to clean up.
So that's all I have today. I want to thank you for watching this video, and check out the resources below. I've got links to all of the date related tips here, on mssqltips.com. Set of all of my bad habits and best practices posts. And then I also have a link to a tip that I wrote about overlapping ranges, and more background on how to calculate those. So thanks again, and I'll see you in the next video.
