//
you're reading...
SQL Server

Date Key Conversions

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.

CONVERT(INT,CONVERT(varchar,DateAdd(d,1,(CONVERT(datetime,convert(char(8),
max(EffectiveDateKey))))),112)) from [dbo].[sometable]

I hope you found this helpful,

Bon Apetite

ldglogo2

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: