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)
SELECT 'Strange Way' AS ResultSet, @DateInt AS IntDate
--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