Printed 2015/10/07 10:26AM

Converting Dates to Integers

By Brian K. McDonald, 2010/10/05

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


            @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


--In my opinion, a better way!



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: | Blogs: SSRSGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald

Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.