I am sure like most of us, if you deal with a data warehouse, your dates are stored in as integers in the form yyyymmdd. As such there is often the need to compare these keys to real date or datetime data types. I find myself constantly looking up how to convert date to int and int to date. I thought I ‘d do a quick post to save you the time.
Convert Date Key (int) to Date:
CONVERT(DATETIME,CONVERT(CHAR( 8), theDateKey))
Convert Date to DateKey (int):
CONVERT(INT, CONVERT(CHAR( 8), theDate, 112))
Normally these converts used to convert a date to datekey when populating the data warehouse or when selecting data out of the data warehouse.
select top 1 * from [FPA].[dbo].v_Client where EffectiveDateKey = cast(convert(char(8), @date, 112) as int)
Here’s a case where I want to pull the latest datekey in the data warehouse and decrement it a day. (used to for a 1 day lag in processing to pick up any transactions that may have come in for the previous processing day late). This code involve converting the date key to a date, decrementing it and converting it back to an integer, basically combining the two techniques above.
max(EffectiveDateKey))))),112)) from [dbo].[sometable]
I hope you found this helpful,