http://www.sqlservercentral.com/blogs/briankmcdonald/2010/10/05/converting-dates-to-integers/

Printed 2014/09/02 01:24PM

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

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

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


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