CONVERT INT YYYYMM TO DATEtime

Question

CONVERT INT YYYYMM TO DATEtime

eseosaoregie

SSCertifiable

Points: 6889
More actions
November 24, 2011 at 3:21 am

#247393

I have recently found some sql script on the net that converts an integer of format yyyymm to a date time. I have used it and it works. HOwever i am not sure entirely sure how it owrks. the code is as follows
CASE
WHEN @MyToMonth between 175301 and 999912 and --why these ranges?
@MyToMonth%100 between 1 and 12 -- this checks to insure there are legitimate months
THEN convert(char(8),dateadd(mm,(((@MyToMonth/100)-1900)*12)+(@MyToMonth%100),-1),112) -- what is the dateadd function doing here
END
I would just like some clarity.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

eseosaoregie (11/24/2011)
I have recently found some sql script on the net that converts an integer of format yyyymm to a date time. I have used it and it works. HOwever i am not sure entirely sure how it owrks. the code is as follows
CASE
WHEN @MyToMonth between 175301 and 999912 and --why these ranges?
@MyToMonth%100 between 1 and 12 -- this checks to insure there are legitimate months
THEN convert(char(8),dateadd(mm,(((@MyToMonth/100)-1900)*12)+(@MyToMonth%100),-1),112) -- what is the dateadd function doing here
END
I would just like some clarity.

The first part of the CASE statement is to check that the "int" is between 1753-01-01 and 9999-12-31 as these are the minimum and maximum dates allows by datetime.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 2

oh ok thanks. and the dateadd function. what is it doing?

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

eseosaoregie (11/24/2011)
oh ok thanks. and the dateadd function. what is it doing?

Sorry, didn't see that part of your question.

@MyToMonth/100 <-- as both of these are INT, this essentially removes the "month" part of the INT, so 201101 becomes 2011.

((@MyToMonth/100)-1900)*12 <-- works out how many months there have been between date 0 and the year of your INT.

(@MyToMonth%100) <-- grabs the month number of your INT

(((@MyToMonth/100)-1900)*12)+(@MyToMonth%100) <-- All together, works out how many months there have been between date 0 and the "date" in your INT.

dateadd(mm,(((@MyToMonth/100)-1900)*12)+(@MyToMonth%100),-1) <-- Adds the number of months that have occurred between your "date" INT and date 0 to date -1 (1899-12-31), this works out an actual datetime from your INT.

e.g.

SELECT DATEADD(MONTH, 1334, -1)

Returns "2011-02-28 00:00:00.000".

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

My first question for you is this, have you looked up the DATEADD function in Books Online? If you have, what don't you understand in the query?

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 5

Sorry folks I get it now. Although I was seeing/reading dateadd in the quesry for some reason I was thinking 'datepart' which is what was confusing me. The explanation above clarifies that. My bad:blush: