Working with BigInt DateTimeID effectivly

  • Article

In many cases the fact table of a data warehouse has to be queried for a specific date range using SQL. There are many ways to compare Date and Time columns in SQL. Using the dim.DateTime dimension from my previous post, the DateTimeID is of type BigInt.

To convert between DateTime and Bigint the following queries are used:

--Today:

Select GetDate()
-----------------------
2009-05-26 11:03:23.023

--To convert this to bigint:

Select convert(varchar, GetDate() ,112)
------------------------------
20090526

--and back to DateTime:

Select convert(datetime, cast(20090526 as varchar) )
----------------------
2009-05-26 00:00:00.000

--To get the Bigint with Time:

Select convert(varchar, GetDate() ,112)+
substring(replace(convert(varchar, GetDate() ,108),':','') , 1, 4)
----------------------
201002181143

--To add three months to the date:

Select convert(varchar, dateadd(m, 3 , cast(20090526 as varchar) ) ,112)
------------------------------
20090826

Wenn das Datum mit Uhrzeit vorliegt, bekommt man den Datumswert ohne Substring einfach und performant durch folgende Formel:

If the BigInt comes with a time part and only the date part is needed simply use

Select cast(200906152359 / 10000 as int)
-----------
20090615

Or to get the start of day as BigInt:

Select cast(200906152359 / 10000 as bigint)* 10000
--------------------
200906150000

End of day will be:

Select cast(200906152359 / 10000 as bigint)* 10000 + 2359
--------------------
200906152359

 

 

Using this SQLs you can simply query things like

Select * from Fact where DateTimeId between 

cast(convert(varchar, GetDate() ,112) +'0000' ) as Bigint

and

cast(convert(varchar, GetDate() ,112) +'2359' ) as Bigint