Convert a date to an integer

In data warehouse land we use integers as primary keys for the date in our date dimension. So we store the date “2008-12-01 00:00:00.000” as “20081201”. This lets you make a fast join from your fact table to your date dimension, because joins on integers are simply faster than joins on datetime fields.

Source systems often store dates in the DateTime format. So we have to convert this DateTime to an integer. You can do that with this T-SQL statement:

1
SELECT YEAR(GETDATE())*10000+MONTH(GETDATE())*100+DAY(GETDATE()) AS DatetimeToInt

Replace GETDATE() with the date field you want to convert to an integer.

But the best way, which I use more and more lately, is this one. This trick converts a date to an integer quickly.

1
SELECT CONVERT(VARCHAR, GETDATE(), 112)

Convert date to integer
You may also want to use this field as a normal date field again, for example in a report in Reporting Services. I do not recommend this, because functions are very slow, especially on large datasets. That is because a function goes through every row of a dataset. If needed, you can do this quite easily by creating the function below:

1
CREATE FUNCTION [dbo].[FromDateIDtoDateValue](@Date varchar(8))
2
RETURNS datetime
3
AS
4

5
DECLARE @FunctionResult datetime
6

7
IF isdate(@Date) = 1 SET @FunctionResult = cast(@Date as datetime)
8
Else SET @FunctionResult = null
9

10
RETURN(@FunctionResult)
11
END

Then you can call this function:

1
SELECT
2
dbo.FromDateIDtoDateValue(20081201)

Frequently asked questions

How do I convert a datetime to an integer like 20081201 in T-SQL?

Use YEAR(date)*10000+MONTH(date)*100+DAY(date). Faster is CONVERT(VARCHAR, GETDATE(), 112). That turns 2008-12-01 00:00:00.000 into the number 20081201.

Why use an integer as the key in a date dimension?

It gives a fast join from the fact table to the date dimension. Joins on integers are faster than joins on datetime fields. That is why the date dimension stores 2008-12-01 as 20081201.

What does CONVERT(VARCHAR, GETDATE(), 112) do in SQL Server?

Style 112 converts a date to the text yyyymmdd. It is a short, fast way to convert a date to an integer. Replace GETDATE() with the date field you want to convert.

How do I convert a date integer back to a date in SQL Server?

Create a function such as FromDateIDtoDateValue. It checks with isdate whether the value is a date and returns NULL if it is not. You call it with, for example, dbo.FromDateIDtoDateValue(20081201).

Why is it better not to use a function to convert a date integer back in a report?

Functions are very slow, especially on large datasets. A function goes through every row of the dataset. So it is not a good idea for reports in Reporting Services.

Convert a date to an integer