The DateTime DataType

To start of with: were you aware that SQL Server has 2 different DataTypes to store DateTime values?

Yes, that is right. The most used DataType is the DateTime. But there is also a DateTime2 DataType. Let's have a look at the differences.

First we will create a Table with one of each and insert some data:

create table DateSamples(
  DateSampleID int identity,
  TypeDateTime datetime,
  TypeDateTime2 datetime2,
  SomeText nvarchar(100)
)

insert into DateSamples(TypeDateTime, TypeDateTime2, SomeText)
values(getdate(), getdate(), 'Today')

select TypeDateTime, TypeDateTime2, SomeText
from DateSamples

Looks pretty much the same, huh? But there is a slight difference here. The DateTime DataType has a precision of 3 for the Seconds, whereas the DateTime2 goes to a precision of 7.

Well that is nice, but in many use-cases that would not be necessary. Right?

That is true. But with the DateTime2 you can also specify the precision for the seconds. So let's assume you just need the Seconds and nothing more than that. We could add a Column for that to our DateSamples Table:

alter table DateSamples add DateSpecific datetime2(0)

update DateSamples
set DateSpecific = getdate()

select *
from DateSamples

As you can see from this example, you now just have the Seconds and nothing more precise than that. As said, in most cases that would already be sufficient. But I did encounter a situation once where we had to import data from a different Application used for very precise testing of products. And yes, in that case we had to use DateTime2 to be able to do an import correctly without losing the precision from the other Application.

But there was another case where I had to make use of the DateTime2. Try this one:

insert into DateSamples(TypeDateTime, TypeDateTime2, SomeText)
values('1000-01-01', '1000-01-01', 'Long ago')

That did not work. Did it? Now try this one:

insert into DateSamples(TypeDateTime, TypeDateTime2, SomeText)
values('1753-01-01', '1000-01-01', 'Long ago')

As you can see, DateTime accepts only values between January 1st 1753 and December 31st 9999. DateTime2 also accepts values between January 1st 0001 and December 31st 9999.

In the case I encountered, it was a Database with Genealogy data for a Family Tree. No date was further back in time than around 1100 if I recall correctly. So using a DateTime2 was sufficient.

So, what to do when you need to store older BC dates? Unfortunately there is no DataType that can handle this in SQL Server. So in that case you would have to split the value up into 3 Integers for Year, Month and Day.

What week is it?

To get a week number, you can use the DatePart Function and ask for the Week. But you can also ask for the ISO_Week. And that can give different results, depending on which year you are looking at. Check this example that will definitely give a different result:

select datepart(Week, '2010-01-01')
select datepart(ISO_Week, '2010-01-01')

According to the ISO Standard, the first week of the year is the week that holds the first Thursday. January 1st 2010 was a Friday, so the Week option tells you it is the first week. The ISO_Week option tells you it is week 53 from 2009.

In general it would be better to use the ISO_Week than the Week option since it is an international standard. But in my experience it is even more important to be consistent with using one or the other. That is, as long as you don't compare the data with another Application.

In that case you should check what that other Application uses to avoid differences in interpreting the data. Sometimes it is better to be consistent than to be right.

What day is it?

Like I said before, January 1st 2010 was a Friday. And Friday is the 5th day of the week, right?

Well, that is true here in the Netherlands and in many other countries as well. But what does SQL Server tells us?

We can check that by getting the Weekday using the DatePart Function:

select datepart(Weekday, '2010-01-01')

Most likely you will get 6, not 5 with this one. That is because by default SQL Server counts Sunday as the first day of the week.

You can overrule this default by setting the DateFirst to 1 to specify you want Monday to be the first day of the week:

set datefirst 1
select datepart(Weekday, '2010-01-01')

This is pretty easy and it gives you the result you want. But alas, it is never this easy.

If you open up a new tab in Management Studio and just run the Select Statement again (without setting the DateFirst) you will see it will return the 6 again, not 5. That is because setting the DateFirst only works within the scope of the Query itself. Other queries will not be affected by it.

There are some other problems here as well. You cannot set the DateFirst in a View or in a Function. And those are just the places where I would like to use it!

But I've tried and it is just not possible. But luckily with the @e and a bit of math it is possible.

select @DATEFIRST
select (datepart(Weekday, '2010-01-01') + @@DATEFIRST + 5) % 7 + 1

Avoid local formats

One common mistake I see is to use strings for a Date in a specific locality. Like I did in the previous examples. But that is just what they are. Examples, not Production code.

For Production it is a whole different case. These might work fine on your machine, but they might give problems on another machine.

Try these ones for example:

set language British
select cast('2022-14-02' as datetime)
GO

set language us_English
select cast('2022-14-02' as datetime)
GO

Now the first one will work because in British the date format is yyyy-dd-MM. But in the US English version it should be yyyy-MM-dd. And since there is no 14th Month, the conversion will fail.

Luckily SQL Server has a better way to do this using the DateFromParts Function:

set language British
select DateFromParts(2022, 2, 14)
GO

set language us_English
select DateFromParts(2022, 2, 14)
GO

Shorthand is not your friend

SQL Server has many ways to make life easier by writing less code. The Date related Functions also have a lot of shorthand versions. But my advice in this case it to avoid these. Here is why.

You would not be the first one to use Y to get the Year from a Date. But compare these:

select datepart(y, getdate())
select datepart(yy, getdate())
select datepart(yyyy, getdate())
select datepart(Year, getdate())

As you can see: just Y stands for the day in this Year, not the Year itself. YY and YYYY are the same. YY does nut return a 2-digit value. I would advice you to just be clear about it and type Year here, like in the last option.

Confusingly the W stands not the Week of the Year but instead for the Day of the Week:

select datepart(w, getdate()) select datepart(Week, getdate())

Other common mistakes are to use mm to get the Month where that should be MM, since mm stands for the minutes. So in general just type Year, Month, Day or whatever you need.

And the oh so common way to get tomorrow or next by using +1 or +7:

select getdate()+1
select getdate()+7

Why not use it? It works fine right?

Are you sure about that? Let's apply this to our table with the DateTime2 DataType:

select
TypeDateTime2,
TypeDateTime2+1,
TypeDateTime2+7
from DateSamples

There you go. The +1 and +7 will work fine with DateTime Columns, but it will fail with DateTime2 Columns. Do yourself a favor and make your Queries Future-ready by using the appropriate SQL Function for this:

select
TypeDateTime2,
dateadd(Day, 1, TypeDateTime2),
dateadd(Day, 7, TypeDateTime2)
from DateSamples

You never know if at some point in time in the future some might change the DataType here. So better be prepared for that.

Between you and me...

Another common mistake I see is the use of the between Function. Let me show you with an example. For this we will insert some extra Rows to our DateSamples Table:

insert into DateSamples(TypeDateTime, SomeText)
values
('2022-01-03', 'SalesOrder 1'),
('2022-01-15', 'SalesOrder 2'),
('2022-01-31', 'SalesOrder 3'),
('2022-01-31 10:10:10.000', 'SalesOrder 4'),
('2022-02-01', 'SalesOrder 5')

Now if we want to count how many SalesOrders we had in January, I often see this one:

select count(*)
from DateSamples
where TypeDateTime between '2022-01-01' and '2022-01-31'

This will give you only 3 because the 4th SalesOrder has a specific time on that day. And that one will be skipped. So, the common correction I see is this one:

select count(*)
from DateSamples
where TypeDateTime between '2022-01-01' and '2022-02-01'

This one is also not correct since it also counts the SalesOrder from February 1st. A better correction would be this one:

select count(*)
from DateSamples
where cast(TypeDateTime as Date)
between '2022-01-01' and '2022-01-31'

And yes, this one will give you the correct result and on this small Table it is lighting fast.

But you are doing an Implicit Conversion. That is: you are changing the DataType as you go which in general is bad for performance. When your TypeDateTime Column has an Index, SQL Server can be very fast by doing an Index Scan. But since you intentionaly change the DataType, SQL Server can no longer use that Index anymore. Now for such a small Table it might not be a problem. But if your Table has millions of records, performance could drop significantly.

So watch out when using the Between Operator for Dates. Instead use Dates and the Greater (or Equal) Than and Less (or Equal) Than Operators:

select count(*) from DateSamples where TypeDateTime >= '2022-01-01' and TypeDateTime < '2022-02-01'

This will give you the correct results and without risking problems with the performance.

Mind you, the Between Operator is still great when you use it for Integers!

That's it for Today!

As you can see there is a lot more to that simple Date than you might think. Because of that, a lack of understanding how Dates behave is a source of many mistakes I encounter when a Query does not provide the results as expected. And please avoid the use of shorthand in your Date Functions. Not only is it harder to read and understand, it is also another source for common mistakes. I hope by reading this you have a better understanding of how to work with Dates in your Queries. And if you want to know more how to format your Dates? Be sure to check this BlogPost I did on that topic.

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!