While working on a project recently, I came across a conversion that was trying to convert a date to an integer value. Much like the value one would see in a Date_SK column contained within the DimDate table in a data warehouse. The conversion was actually quite strange and I was thinking to myself that this could be much easier. Below I have a script that shows you the way that I found as strange and what I believe to be an easier way to convert it.

Script 1: Strange Way and My Way Comparison

DECLARE

            @DisplayDays TINYINT = 31

            , @DateSince SMALLDATETIME

            , @DateInt INT

--Get me the date to start from based on the days to display         

SELECT @DateSince = CONVERT(VARCHAR(10),DATEADD(d,-@DisplayDays,GETDATE()),101)

--Now this is the strange part! Do you concur?

SELECT @DateInt = YEAR(@DateSince) * 10000 + MONTH(@DateSince) * 100 + DAY(@DateSince)

--Return results

SELECT 'Strange Way' AS ResultSet, @DateInt AS IntDate

UNION

--In my opinion, a better way!

SELECT 'My Way' AS ResultSet, CONVERT(INT,CONVERT(VARCHAR(8),DATEADD(d,-31,GETDATE()),112)) AS IntDate

Figure 1: Results

What do you think? Is my way a little easier to understand? I sincerely hope that you have enjoyed this quick blog. If you did, please rate it! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald.

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com | Blogs: SSRSGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald