Why are you still using datetime?
28November 10, 2015 by Kenneth Fisher
It’s almost Thanksgiving time again! Let’s see, what am I thankful for? T-SQL Tuesday! Someone else get’s to pick a blog topic for me! In this case it’s the always fun Mickey Stuewe(b/t) and her topic is Data Modeling Gone Wrong. So let’s see .. a Data Model is a small toy data right? Ok, so maybe it’s not. Definitions are not my strong suit. So what is Data Modeling? The simplest definition I could find for my purposes is this:
Data models define how the logical structure of a database is modeled.
So basically, if I understand it correctly, data modeling is in part the database design. And as it happens I’ve had a question about database design (gone wrong) for a while.
Why is everyone still using the DateTime datatype exclusively?
Back in SQL 2008 we gained a whole new range of date/time datatypes. Isn’t it about time we started to use them? Don’t get me wrong, datetime is still useful, if you actually need accuracy to three thousandths of a second and you aren’t going too far back into the past (earlier than 1753). Oh, and you only care about one time zone. Frequently though we don’t really care about the time (date of birth, credit card activation date, etc) in which case we can use the Date datatype. This has some major advantages. DateColumn = ‘1/1/2012’ will actually give you accurate answers for example. Not to mention the 5 byte size savings (8 byte datetime – 3 byte date). But what if the time is really important (shift or time of day calculations)? If so use the Time data type. This way you can put an index on the time specifically. Need to work with time zones? DateTimeOffset. Need the date with a lower accuracy time (seconds)? SmallDateTime, and that one’s been around since 2005. And last but not least, what if you need really high precision time? DateTime2 can take you down to a ten millionth of a second.
So let me ask again. Why is that column a datetime?
SQL Studies 
Why? because I was lazy! My good intentions for the coming year: use those types. Thanks for reminding me
Glad to hear it!
Better a datetime than an int or varchar interpretation of a date that I see in my job:
WHERE EffDate = 20151112
or
WHERE EffDate = ‘20151112’
How do you add a day to 20151130? Ugg.
I worked at a place where we had 6 columns for each date (and a good reason for it). We had datetime, date as int and time as int. Then the whole thing over again as UTC. Made for some big tables but unfortunately we needed them. You’d be amazed how hard shift calculations are on a regular datetime .. and this was back in SQL 2000.
The history of Sybase/UNIX and the DEC hardware that lead to DATETIME in SQL Server is interesting. But today it is like using gaslights instead of LEDs in your house. Look at how much code is used to trim the time portion back to 00:00:00.00 to fake the simple DATE data type.
Yep. Date vs DateTime was actually the inspiration for the post. Life would be so much easier if some of these tables had just been constructed right in the first place!
[…] Kenneth Fisher says to stop using datetime and start using date, time, and datetime2(x): […]
Why? Because I have to support customers of our software on their platform of choice, and I’d rather do it with one codebase.
I was finally able to convince our powers-that-be to stop supporting SQL 2000 only 3 years ago, and it was only last year that the current version of our software stopped supporting SQL 2005.Now that I’m up to a base of 2008, I can _start_ looking at a quarter million lines of code to see where “new” features like time and date could be used – without breaking the other tiers of the application.
Fair enough. And I certainly don’t expect people to go back and change all of their legacy code. I was talking about new code being written in versions of SQL where these new data types are available.
I’m working on a project being developed using Visual Studio 2012, where the database is built from classes in the project. The database being built is SQL Server 2012. When I ask the developers defining these classes why they’re using datetime for columns that only require a date (like a birthday), they tell me that VS 2012 doesn’t support the newer date data types. So of course, I’m constantly casting datetime value as dates, so I can do comparisons. Are they being less than honest with me?
The date datatype isn’t new to SQL2012 (came out in 2008 I think), so why can’t they use that instead of datetime? Have them show you the error message they are getting. Also, ask them if they have the latest SSDt extension installed.
I’m guessing it’s this: “if they have the latest SSDt extension installed”, as they are very aware that the date data type is available, since I ask them about this every time I get tired of typing Cast(DOB as date), which is fairly often. Thanks for responding.
This is the answer I got when yet another datetime column with no need for the time was added to the database:
We Use Entity framework to create or make any changes to database , Date datatype is not supported in entity framework or in .net framework.
Give them this: http://stackoverflow.com/questions/5658216/entity-framework-code-first-date-field-creation
Of course you could also use this as a reason why they have to stop using EF 🙂 (It isn’t capable of using the modern database features)
Thanks so much. Although I disagree on why EF is not a good idea. To me, the two scariest words uttered by programmers are “code first”. (It should be followed by “ask questions later”.)
I probably should have worded that better 🙂 I have nothing against EF in general. I don’t really know it well enough to have an opinion. It was mostly meant as a joke (my sense of humor is known to be questionable)
I agree with all your sentiments except in relation to smalldatetime. Any datatype that has built-in obsolescence within the next 100 years is off limits in my view. It produces an overflow for any date beyond the year 2079.
You certainly have a point. Although I will say the chances of a DB system still being around in 60 years is pretty slim.
I completely agree here. I abandoned datetime in favor of datetimeoffset and even GETDATE() in favor of SYSDATETIMEOFFSET(). I was compelled to do this to get around how PHP reformats date/time. The result is an unambiguous date/time. Definitely worth the tiny bit of extra data space taken.
Don’t forget that datetimeoffset has an adjustable scale. So datetimeoffset(2) gives you 100ths of a second and is 8 bytes (same as datetime).
I’m only answering why I haven’t, not suggesting to anyone that they stop or start using the new datatypes, but I thought this info might help drive discussion and new features (one I’m hoping for is the implicit conversion of 0 to ‘1900-01-01’.
So to the question, “Why are you still using DATETIME?”
While I don’t have a hard rule to always use DATETIME, I do use the other datatypes, just not by default. I still use DATETIME as the norm.
I’ve started using DATE or converting to DATE to strip time when valuable (a discussion for a different forum topic is I always capture time, I let the app decide if it wants to treat everything as date only but if we don’t capture it, it’s gone forever and later someone may care – I know same thing can be said for precision).
I really do want to start using the new types more regularly but the value they add hasn’t come up as necessary and the change to habits required really need a value add to put that effort in. I know these aren’t hard to get around, but they do require a change in habit and the need hasn’t arisen, I mean a need that says, DATETIME just won’t work anymore, you have to use the other and that just hasn’t happened (as a norm).
An argument for DATETIME2 is the nuances of the .003 rounding which is really the only useful (I state useful for those of us tracking dates after 1753 which is pretty close to everyone) advantage to DATETIME2. Mostly, we don’t need the precision (if you do, then the precision is a compelling argument for DATETIME2). The timezone argument against DATETIME is invalid in my opinion because UTC handles that. We’ve been storing times in UTC since Y2K and to handle interstate and international business so that’s already been handled. The biggest roadblock for me though is a picky thing I know, but I like to be able write coalesce([MyDateColumn],0) instead of typing out the whole date and time string ‘1900-01-01 00:00:00.000000…’.
So far DATETIME has been sufficient for all my needs, especially as a standard. DATE has had some compelling reasons for me to use, but not DATETIME2 or OFFSET and SMALLDATETIME I’ve never ventured to look into.
Another reason, which applies to very few people, is I still have 2000 and 2005 servers to support. A lot of my code has to work in every environment so this is a road block for picking up a new practice/habit.
The adventurer in me is looking for that case where I have to use the new types, because I like new things, but I don’t implement new things just because they’re new and cool, I implement them because they add real value, are the best or the only way to provide a solution (subjective measures I know) . The scalability and future proofing is compelling, but I’m still on the fence about INT vs BIGINT vs UNIQUEIDENTIFIER for future proofing, so there ya go.
Hmm. I do want to point out that datetime2 and datetimeoffset have adjustable precision so you can pull it back to just seconds if you want to. Also while I certainly understand the argument that if you don’t collect the time it’s lost forever it doesn’t really apply if the date in question is part of a data entry form. No one I know is going to include the time when asked for their birthdate for example. And as I alluded to in the post you have to have special coding structures to eliminate or account for the time which typically slows things down.
Again, not trying to argue for or against DATETIME just saying why we haven’t changed it from our standard. I’m aware of the precision setting, but it would be a new habit, and frankly, we wouldn’t specify precision our dates would just get longer on the screen, that’s not a big deal. The main reason for me is really the implicit conversion of 0. If that was handled I’d probably make DATETIME2 the standard.
There are many advantages to using DATETIME2 or OFFSET but so far, none of the advantages have made it worth switching as standard. The manipulation of time strings to add or remove time or get precise intervals is where we have used DATETIME2 and DATE.
There is this nagging thing in the back of mind that knows Microsoft didn’t add them because they were bored, they added them to fill a need yes, but also to move down their roadmap, so I have to wonder if or when the late adoption is going to bite me and how. What is coming down the pipe that is going to make us all care about a tick (10 millionths of a second)? When I worked with audio and screen recording software that would have been useful, it’s not currently useful in most cases, but is it going to become so?
[…] Fisher Why are you still using datetime? Kenneth brings a great question to the table. Why are you still using datetime? He goes into the […]
Good points! Honestly, the only reason I still use DateTime is habit. In my defense, I’ve been developing these habits for a long time – the first job I had we were running a mix of SQL Server 6.5 and 7.0 – so you can probably guess how many years I’ve been doing this.
But it’s time to change some habits.
Habit is actually why I did the post actually. And don’t get me wrong, I’m guilty too 🙂 We do better about strings and numbers but frequently forget that there are other options with dates 🙂
I often blog to help me remember things or to help me change habits. It works pretty well, really. Writing makes thoughts more concrete.
Yep. I agree completely. Probably 1/3 of my posts are just notes for myself for the future.