Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Converting Dates to Integers

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

Comments

Posted by Steve Jones on 5 October 2010

Definitely easier to understand. Someone, IMHO, would be confused by the math in the "strange way."

Posted by Brian K. McDonald on 24 October 2010

My thoughts exactly Steve! I'm still looking forward to someday getting to meet you face to face. Are you going to SQL Saturday in Tampa?

Leave a Comment

Please register or log in to leave a comment.