Love Your Data Types: Dates
Video Summary
In this video, I delve into the often-overlooked world of date and datetime data types in SQL Server. Erik Darling from Darling Data shares his insights on why these data types are frequently mishandled and how to properly manage them for accurate results. We explore the nuances of date formatting across different languages and regions, emphasizing the importance of using unambiguous date formats to avoid sorting issues and other anomalies. I also discuss Microsoft’s recommendation to use `datetime2` over `datetime`, highlighting its advantages in precision and portability, which are crucial for globally deployed applications. The video covers practical tips on using the `convert` function with appropriate styles to ensure your dates are handled correctly, making it a must-watch for anyone working with date data in SQL Server.
Full Transcript
Erik Darling here with Darling Data. Feeling extra optimistic about this video. Why is my phone being? Anyway, in today’s video, we’re going to talk about continuing to love our data types. And we are going to do that today with dates. So we have much to go over here. So get a coffee or something. Down in the video description, all sorts of ways that you can help me pay for stuff. You can hire me for consulting, buy my training, become a supporting member of the channel. And of course, if you just want to harass me with questions, you can do that. There’s a link. Ask me office hours questions. Without you, we have no office hours. I’m not asking myself questions. That would be weird. I don’t want to think about that world. And of course, if you enjoy this content, please do like, subscribe, tell a friend, all that stuff. We are well on our way to having nearly 7,852 subscribers, which is miraculous in this day and age.
Out in the world, data to Nashville, data Saturday, Chicago, that’ll be out in March. I’ll have some new stuff to add to this slide shortly. There have been some other stirrings and acceptances. I don’t know. I don’t know how I’m going to do that. I might need, I might need two slides to talk about all the stuff that I’m going to be doing. Maybe even three. We’ll see. If I can only fit two per slide, man, we’re in trouble. It’s going to be, it’s going to be a crazy year for darling data getting out in the world. Tell you that much. But for now, let us cherish the spirit of the season and talk about dates.
Now, dates are without question the data type or dates and date times and all that stuff are without question the data type that I see people behave the laziest with. The absolute laziest. And SQL Server is partially to blame for that because SQL Server goes out of its way to make this easy on you. There are all sorts of crazy things SQL Server will do to accommodate whatever nonsense you type into your strings and expect it to infer the date timiness of.
So, first, let’s just start by using this function, sys.dm exec describe first result set. And we have some candidate dates in here. We think these are very datey. We think SQL Server should be able to date these appropriately. But alas, all of these columns come back as being various takes on varchar. We have one that is an eight. That is this one right here.
And then we have a bunch of tens. Just because you type a date or a date time into a string does not mean that SQL Server automatically says, Oh, look at this wonderful date or date time that this caring user typed in here. I will infer it as such. It does not do that. All right. So, sometimes we might have to tell it or nudge it or give SQL Server a little bit of extra information in order for it to understand what we’re after.
Using the convert function, even without a style here, but I am a big proponent of using styles. We just can’t use one here for reasons that we’ll discuss. If we do this, then all of a sudden SQL Server figures out, yes, these are all dates.
Thank you for pointing that out, right? We have successfully converted our strings to the correct data type with the magic of the convert function. Wonderful stuff. Wonderful.
It’s fantastic the things you can do when you type a little bit more. The reason why we can’t use a style on these, though, is because, well, you know, I’m an American and, you know, my country tis of thee and all that. But we are completely alone in our treatment of the date of date formatting.
Let’s do our magic SSMS 22 zoom here. We are the only one. And while the USA is number one, it is quite lonely on top, at least in this DMV.
You’ll see that U.S. English is the only one where the date formatted is treated as month, day, year. There are 29, I think, if I’m remembering correctly, sorry, 24 rows, 24 rows where the date is treated as day, month, year in the format. Some friends of ours, like the British and the Danish and even I have one Czechoslovakian friend, so we’ll.
Or Czech Republican friend, what do you call them these days? I don’t know. Those maps keep changing.
It happened to Gorbachev. But there’s a whole bunch of languages in here that do not accommodate the month, day, year format. There’s many things in here that, well, some of these quite suspect, quite suspect typing in there, but that’s not the point.
Anyway, there are even countries that do things without MDY or DMY. There are some that do YMD, like our friends the Croatian-Lovakians and the Lithuanian-Lovakians, right? The Swedish, who are not Swiss, right?
So all of these different countries and languages, I shouldn’t say countries, these are languages, right? I mean, some of them are, you know, specific to a country, but some of them are definitely multi-country languages. But these ones are treated even different.
And like something I didn’t point out in the others, but there is even some disagreement. Even once we have sorted out the date format that we want to use, some people disagree about which day should be first in the week, Monday or Sunday. So we don’t, so there is disagreement about how this gets treated, even when we have agreement on date format.
So you have to be very careful out there when you’re building SQL Server applications and queries to make sure that when you pass in dates and date times and all those other things, that you are quite careful to write unambiguous dates that cannot be confused. Because there are many ambiguous dates.
Now, we’re going to go back sort of to our original problem, which is that SQL Server does not automatically infer dates just because a string looks like a date, right? So we have three dates here. We have December 1st, 2025.
And then, well, I guess there is ambiguity in either of these and all of these, right? Because this could be December 1st of 2025. This could also be January 12th of 2025.
And likewise, these could be January 2nd or February 1st of 2025, 2026. We don’t know, right? But the point is that without SQL Server knowing these are dates, SQL Server sorts these as strings, which is completely understandable.
I would not want to figure this out either, right? So 12 gets sorted down here, even though technically that year is earlier. We do not automatically infer any datiness from our strings.
Which gets even weirder. I mean, not weird like bad weird, but just like where you might find weirdness in your applications if maybe someone from outside of America uses your application. You might find some oddities if you are not passing in dates, again, in a consistent, unambiguous format.
So let’s look at this. So we’re going to set the language to English, which, you know, yeah, U.S. English, right? Proper English.
But over in our results pane, right? We get back these three rows in order, one, two, three, which as far as the U.S. dating system goes, this is correct, right? We have December 1st of 2025.
We have January 1st of 2026. And we have February 2nd, sorry, February 1st of 2026. So according to U.S. dating policy, this is correct.
But according to our friends in Britain, this is different. And I assume that British also encompasses Canada, Australia, New Zealand. And I don’t quite know where else.
But let’s just go with those ones. If we set the language to British, right, we look over in the messages tab, we will see we are very British now. We are hip, hip, cheerio British.
And we look in our results. The row ordering has changed, right? Before it was one, two, three. Now it is one, three, two. Right? We still, well, I mean, this changes. I mean, this is no longer December 1st of 2025.
This is now January 12th of 2025. But these two rows here that were ambiguous to us, right? These flipped, right?
Because according to British dating policy, these are different from American dating policies. And so these dates, they are not what we think they are. Actually, none of these dates are what we think they are.
We have been lied to. We’ve been run amok, bamboozled, hornswoggled. I forget how the rest of that thing goes. But all of those things, right? Swindled.
Swindled. So really, what you should always do is put the year first. Four-digit year first. That will at least buy you some forgiveness from me. As if you always put the year first.
Because you reduce ambiguity when you put the year first. Where things may still be ambiguous. And let’s just, let’s quote this out so it doesn’t get in the way. Sorry, English.
We’ll come back to you, I promise. Where things that remain ambiguous is with sort order. Because if we set the language to British, we do not have a reliable sort order for our rows, for our duplicates. Right?
Because, you know, these dates basically repeat. Right? Like row one is 2025, 1201. Row four is 2025, 1201. There’s some dashes in here for some of these.
So you can even be forgiven some dashiness if you, if you, long as you put the four-digit year first. But if we want, if we want unambiguous sorting, then we also need to have a deterministic way to sort this data when we encounter duplicates. So remaining with U.S.-British, now we get the data back in 142536 instead of 415263.
But these dates have all been applied correctly to us. Oh dear, I’m getting some green screen artifacts. We’ll have to stay close to the camera so we don’t mess anything up.
But if we bring, if we set this to U.S. English now, again, the number one English, we will get back the same thing without the ambiguity. SQL Server is no longer switching things around on us. Right?
So things came back the way that we expected them to. Where things get, so we’ve just been talking about dates. And what’s crazy, wild to the extreme, is that Microsoft considers date to be a more modern data type than date time. We’re going to talk about that a little bit more in a minute.
But date time is technically a legacy data type. And Microsoft even says you shouldn’t use it anymore. Right? Don’t use date time. Use date time 2 instead with whatever precision you need.
It goes from 0 to 7. Do all sorts of things with it. But date time is no longer good to use. And that’s especially painful for me to talk about because the very, wow, this green screen is going crazy on me here. What’s happening?
There we go. Maybe I should, maybe I’ll stay here. The reason why that’s so painful and crazy to me is because the Stack Overflow database that I use, all of the date columns are date times. And I hate it because I look at them and I have to write procedures that use date time.
Because who has the patience to convert all that stuff over? Do you have any idea how many demos I’d have to change? It would be a nightmare.
Right? And then if I did that and I asked anyone else to look at something and their copy still had date time, who knows? Right? So it would just make no sense. But anyway, we’re stuck with date time in Stack Overflow land. We’re also stuck up going up to, we’re also stuck with the world ending in 2014.
So we got an assortment of problems. But anyway, where things get even weirder is when you get dashes involved with date times. Right?
So what we’re going to do here is we’re going to look way ahead to, this is the final Friday the 13th of 2026. It will be in November. November 13th of 2026.
That’ll be a Friday the 13th. And if we try to do this in English, this will work. SQL Server is like, oh, you’re an American date. Gotcha.
No problem. Right? So US English, this is totally fine. This arrangement works beautifully. But British English, this stops working when we put dashes involved and we try to convert to a date time. This now returns a null because there is no 13th month.
There is no 11th day in the 13th month. I’ve heard various rumors that if we had 13 months in the year, that we would have, all of them would have 30 days. And all of them would start on a Monday and end in a Friday.
And I’m like, wow, we’ve really made things complicated for ourselves. All these 12 months and various other things. So I don’t know.
Maybe, maybe that would be better. I don’t know. Anyway. I’m not even sure if that’s true. Maybe I was lied to. Maybe Wikipedia lied to me. There are some things that I want to talk about before we end this video.
The first one is I hate the cast function. Cast is for lazy dummies. And I say this having been a lazy dummy many times in my own past. Having to be confronted with old code that I’ve written where I see cast use.
And I’m like, man, I screwed up. So learn from me. Learn from the patient zero lazy dummy. Don’t use cast.
Use convert. Convert has styles. And those styles can be very, very useful when trying to figure out if your things are ambiguous. I mean, there’s all sorts of things that you have to get crazy with. And then when you’re dealing with binary and XML and other stuff.
But those are outlandish cases. For the general SQL Server user, you will care very much about styles for having unambiguous date formats that don’t mess things up. For example, 112 is great if you just need dates.
121 is actually the default for time. Date. DateTime2 and DateTimeOffset. Great stuff.
Right? And 127 is ISO 8601 with timezone Z. Right? So this thing at the end. Right? Timezone Z. So very important stuff here.
If you go to the documentation for, well, I mean, we should just not, like, really the documentation that we care about is for convert. Cast has very little documentation. It’s just garbage.
Don’t use cast. Lazy. The documentation for convert has all sorts of helpful things that can help you use convert better. Right?
So we do care about that. Another thing, and this is coming back to something that I said earlier that I didn’t, I said, we’ll talk about this in a minute. And here we are talking about this in several minutes. But, okay.
And that is at the documentation for DateTime. Right? And that is avoid using DateTime for new work. Right? Do not do it.
Right? It is a legacy data type. It is no longer good enough. Instead, use the time, date, DateTime2, and DateTimeOffset data types. These types align with the SQL standard and are more portable.
Time, DateTime2, and DateTimeOffset provide more seconds precision. DateTimeOffset provides timezone support for globally deployed applications. And, gosh darn it, don’t we want our applications to be globally deployed?
Why settle for dominating a single market when you can dominate them all? Right? Why would you want to do that? So, I told you this video would be a little bit more involved than the string one.
I didn’t lie to you. Please, do not use ambiguous dates or date formats in your code. Um, I’ve seen so many dumb things crop up over the years, either because of implicit conversions, or a lack of implicit conversions, or errors, or just, like, things not functioning correctly because people decided to type dates stupidly.
And they paid dearly for it. They paid me dearly for it. Which I appreciate. But, you know, if you want to avoid paying me dearly for these things, use consistent, unambiguous date formats.
And please, use the convert function with a style. Just, I ask very little of you. Please do these things.
Anyway, thank you for watching. Hope you enjoyed yourselves. I hope you learned something. And I’ll see you in tomorrow’s video, where I’m going to go crazy and I’m going to defend merge. Alright.
Adios.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.