Obscure Problems and Gotchas

Smalldatetime has been obsolete for a long time now get with the program

A major pet peeve of mine is the smalldatetime datatype from SQL server. Not because it exists or it was what was available at one point, but the fact that people keep using it. You aren’t doing anyone any favors by using smalldatetime because it is an obsolete data type and worst of all it ignores seconds.

It really shocks me when I speak to DBA’s, seasoned developers and people who think they are architects when they are still using smalldatetime because they think, “I’m saving space because it’s smaller.” It’s the same poor argument that I have seen people give when they claim that using GUIDs as a primary key for everything is the way to go. The truth is they don’t have a valid reason for doing it, it’s just a bias and but they stick with it because of “reasons”.

Smalldatetime had it’s place in history

I am fully aware that this is just an older data type and it had it’s time and place in history, but you can make that same silly argument for knob and tube wiring. Knob and tube wiring had it’s place in history until it started burning people’s houses down to the ground and it became imperative that knob and tube wiring be completely replaced because it was obsolete. So just think of smalldatetime as knob and tube wiring – it’s garbage, stop using it because it will burn your house down.

Why you should discontinue using smalldatetime

First and foremost Microsoft says to STOP using the following things all together directly on the smalldatetime’s page in the microsoft docs (FKA msdn): https://docs.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ver15

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

This is pulled directly from their website. Really I can end the article here, but for some boorish personalities this isn’t enough apparently.

Here are some technical examples of why smalldatetime is garbage

• Max date limitations
• Lack of proper support for seconds
• Ignores date math
• It’s lack of proper support for seconds has caused it’s own error edge case

Date part

• It has a maximum date range of 1900-01-01 00:00:00 through 2079-06-06 23:59:00
• This means as of 02/18/2020, in about ~59 years this datatype is going to be entirely useless all together. Stop using it now!
• From the coding perspective on why this is annoying, C# has a maximum of “9999-12-31 23:59:59” which means that you will now have to make sure that everywhere in your code, you never go above 2079-06-06 23:59:00.

Time part

• On the Microsoft documentation it says that smalldatetime supports 00:00:00 through 23:59:59, but this is misleading and I will demonstrate why.
• The seconds unit is largely ignored and when it isn’t being ignored it is causing problems.

Declaration	Output	Problem
1900-01-01 00:00:01	1900-01-01 00:00:00	You lost your seconds
1900-01-01 00:00:29	1900-01-01 00:00:00	You lost your seconds
1900-01-01 00:00:30	1900-01-01 00:01:00	Rounded up to a minute
1900-01-01 00:00:59	1900-01-01 00:01:00	Rounded up to a minute

If you want to try this for your self go right ahead:

DECLARE 
	 @dtm01Second SMALLDATETIME = '1900-01-01 00:00:01'
	,@dtm29Second SMALLDATETIME = '1900-01-01 00:00:29'
	,@dtm30Second SMALLDATETIME = '1900-01-01 00:00:30'
	,@dtm59Second SMALLDATETIME = '1900-01-01 00:00:59'

SELECT 
	 @dtm01Second AS T00s
	,@dtm29Second AS T29s
	,@dtm30Second AS T30s
	,@dtm59Second AS T59s

• Therefore there is no realistic support for seconds with smalldatetime.
• Also smalldatetime will round up to the next minute when you go over 29 seconds. I don’t see how that is useful at all.

Date math is completely ignored and unreliable

There is a very common way for developers to get the end of the day time from a date. Whether it is for getting the last day in the month or getting the end of the day of a day it’s the same trick. For argument’s sake let’s focus on getting the end of a day with a date that has provided no time. You do the following:

1. Add one day
2. Subtract one second
3. This gives you the end of your day

Here is a C# example:

var dtm = DateTime.Today.AddDays(1).AddSeconds(-1);

This will yield Today’s date and the time of 23:59:59 which is the end of the day before tomorrow. Mind you this only works if you are guaranteed to have no time provided, otherwise I strongly recommend just re-constructing the date yourself and avoiding the math all together.

Here is the tSQL equivalent:

-- Notice I am NOT using DATETIME!
DECLARE @dtm DATETIME2(0) = '1900-01-01 00:00:00'

SET @dtm = DATEADD(SECOND, -1, DATEADD(DAY, 1, @dtm))

SELECT @dtm

This will yield 1900-01-01 23:59:59 (as expected), but this is using a DATETIME2(0) data type (WHICH YOU SHOULD BE USING NOW RIGHT? RIGHT??!?!??!?!?!) but let’s see what happens when you use it’s prehistoric predecessor SMALLDATETIME

-- This is the same code, I just changed the type
DECLARE @dtm SMALLDATETIME = '1900-01-01 00:00:00'

SET @dtm = DATEADD(SECOND, -1, DATEADD(DAY, 1, @dtm))

SELECT @dtm

The yield is 1900-01-02 00:00:00…

How helpful…

The real maximum date value for smalldatetime

Now with all of the above knowledge in your head this last pitfall shouldn’t surprise you, but it does anyhow because what I am going to show you below is just stupid. I think the error returned could be far more clear.

Small datetime’s maximum date is 2079-06-06, however you can tack on some extra time there to make it a little longer. It actually goes up to 2079-06-06 23:59:00 reliably. Because of how smalldatetime likes to round anything over 30 seconds to the next minute, it blows itself up if you run this code:

-- This will execute just fine because it rounds down to 00:00:00
DECLARE @dtmLargest SMALLDATETIME = '2079-06-06 23:59:29'

SELECT @dtmLargest

GO

-- This will blow up because anything between 30-59 seconds inclusive will -- round to the next minute which is outside of it's supported range
DECLARE @dtmBlowsUp SMALLDATETIME = '2079-06-06 23:59:30'

SELECT @dtmBlowsUp

The error you get for running the second batch is this one:

Msg 242, Level 16, State 3, Line 29
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

It would be nice if it could just say don’t provide anything larger than 2079-06-06 23:59:29 because in the real world seconds is a thing we observe.

Conclusion

If you didn’t know now you know… STOP USING SMALLDATETIME!