Understanding CAST(INT AS DATETIME)

Question

Post reply

Understanding CAST(INT AS DATETIME)

thomashohner

SSCrazy Eights

Points: 9856
More actions
May 20, 2015 at 6:52 pm

#297111

While trying to solve a SQL challenge I found myself trying to understand what is happening when you CAST a INT to date time.
Trying to understand the results.
Here are some random numbers and Castings. My question is why do they produce the datetimes they do?
SELECT CAST((1.1) AS DATETIME)
SELECT CAST((200) AS DATETIME)
SELECT CAST((15) AS DATETIME)
SELECT CAST((99.99999) AS DATETIME)
***SQL born on date Spring 2013:-)

Viewing 14 posts - 1 through 13 (of 13 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Day 0 is 1/1/1900. 1 = 1 day...

SELECT CAST(0 AS DATETIME) -- 1/1/1900

SELECT CAST(1 AS DATETIME) -- 1 day afterr 1/1/1900

SELECT CAST((1.1) AS DATETIME) -- 1.1 days after...

SELECT CAST((1.5) AS DATETIME) -- 1.5 days....

SELECT CAST(-365 AS DATETIME) -- A year before...

SELECT CAST(365 AS DATETIME)

Applies to getdate() as well

SELECT getdate()-0 --now

SELECT getdate()-1 -- yesterday at this time

SELECT getdate()+1 -- tomorrow at this time

SELECT getdate()-1.1 -- 1.1 days ago...

SELECT getdate()-1.5

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 2

Thanks Alan,

So is it correct to say that when adding or subtracting from datetime the default is Days?

***SQL born on date Spring 2013:-)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 3

Here's another set of examples...

SELECT DATEADD(yy, DATEDIFF(yy, 0, CURRENT_TIMESTAMP), 0) AS FirstOfCurrentYear

SELECT DATEADD(qq, DATEDIFF(qq, 0, CURRENT_TIMESTAMP), 0) AS FirstOfCurrentQuarter

SELECT DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0) AS FirstOfCurrentMonth

SELECT DATEADD(wk, DATEDIFF(wk, -1, CURRENT_TIMESTAMP), -1) AS FirstOfCurrentWeek -- 1/1/1900 was on a Monday... -1 gets you to the previous Sunday

SELECT DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0) AS FirstOfCurrentDay

That said, I think it's generally considered best practice NOT to use integers in place of dates. #1 it can be confusing to other's who aren't acquainted with it and #2 "To the best of my knowledge"... It's not officially sported behavior and potentially subject to change in future releases...

Note: I said, "to the best of my knowledge". If documentation to the contrary exists, please post it.

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 4

This was a SQL puzzle that I was trying to figure out. This was one of the pieces. Trivia question basically not a real scenario.

***SQL born on date Spring 2013:-)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

Yep

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Definitely a poor practice (it's confusing) and only works with DATETIME, not DATE, NOT DATETIME2, not DATETIMEOFFSET

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dohsan SSCrazy Points: 2972 More actions · Answer 7

For the trivia, I'll also add you can CAST minus numbers to get a date previous to 1900

SELECTCAST(0 AS DATETIME);

SELECTCAST(-53690 AS DATETIME);

SELECTCAST(-53691 AS DATETIME);

Furthest you can go back is 1753-01-01 00:00:00.000, which is deemed the time that we switched from the Julian to Gregorian calendar.

For trivia purposes found this link that explains it more!

http://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Food for thought:

SELECT DATEDIFF(millisecond,'20150521','20150522') --86400000 ms in a day

SELECT

RightNow, RightNowNumeric, RightNowDecimalPart, RightNowIntegerPart,

ReconstructedDate = DATEADD(day,RightNowIntegerPart,0),

ReconstructedDateTime = DATEADD(millisecond,86400000*RightNowDecimalPart,DATEADD(day,RightNowIntegerPart,0))

FROM (

SELECT

RightNow,

RightNowNumeric = CAST(RightNow AS NUMERIC(18,12)),

RightNowDecimalPart = CAST(RightNow AS NUMERIC(18,12))%1,

RightNowIntegerPart = CAST(CAST(RightNow AS NUMERIC(18,12)) - CAST(RightNow AS NUMERIC(18,12))%1 AS INT)

FROM (

SELECT RightNow = GETDATE()) d

) e

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

GilaMonster (5/21/2015)
Definitely a poor practice (it's confusing) and only works with DATETIME, not DATE, NOT DATETIME2, not DATETIMEOFFSET

"It Depends".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 10

The original Trivia question was for a $50 Amazon gift card.

It was why does MONTH(18/200) AND MONTH(200/18) both return 1's.

CAST INT to Datetime was part of me trying explaining my answer.

***SQL born on date Spring 2013:-)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

thomashohner (5/22/2015)
The original Trivia question was for a $50 Amazon gift card.
It was why does MONTH(18/200) AND MONTH(200/18) both return 1's.
CAST INT to Datetime was part of me trying explaining my answer.

Too bad I didn't know about that. 😀

The division inside the parentheses will occur first and both are integer division because the literal whole-number operands (Dividend and Divisor) will be interpreted as being of the Integer datatype.

18/200 is like asking "How many times does 200 evenly go into 18. The answer (Quotient) is 0 and that will also be interpreted as an Integer. Any time you pass a date function an integer, the integer will be interpreted as the "number of days since midnight the first of January, 1900". So this date will be interpreted as 1900-01-01, the MONTH of which is "1".

All the same holds true for 200/18 except the answer is "11", which will be interpreted as 1900-01-11 by the MONTH function. Of course, this date is still in January, so MONTH will also return a "1" in this case.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 12

My answer probably has some logic holes in it and not as elegant as yours Jeff, but this is what I put

When using the Month(18/200) or (200/18) the Resulting Int/Decimal value is first converted to the DATETIME Data type the month is then taken from the Datetime converted datatype. Try this SELECT CAST((18/200) AS DATETIME) SELECT CAST((200/18) AS DATETIME) We then end up with the date “1900-01-01 00:00:00.000” and “1900-01-12 00:00:00.000” Where the month is January. However if we change the number to something like SELECT CAST((99.99999) AS DATETIME), MONTH(99.99999) we get April Let me know if I need to get more detailed

"The default for an invalid DATETIME is “1900-01-01”, the default for NUMBERS and DATES is to add DAYS. So the answer will be “1900-01-01” + whatever the resulting number is in days. Then it takes the MONTH from that result. In this case SELECT MONTH(200/18), CAST((200/18) AS DATETIME) Is one day + “1900-01-01” which is still the month of January"

Just a reminder self taught only 2 years take it easy.. :w00t:

***SQL born on date Spring 2013:-)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

thomashohner (5/22/2015)
My answer probably has some logic holes in it and not as elegant as yours Jeff, but this is what I put
When using the Month(18/200) or (200/18) the Resulting Int/Decimal value is first converted to the DATETIME Data type the month is then taken from the Datetime converted datatype. Try this SELECT CAST((18/200) AS DATETIME) SELECT CAST((200/18) AS DATETIME) We then end up with the date “1900-01-01 00:00:00.000” and “1900-01-12 00:00:00.000” Where the month is January. However if we change the number to something like SELECT CAST((99.99999) AS DATETIME), MONTH(99.99999) we get April Let me know if I need to get more detailed
"The default for an invalid DATETIME is “1900-01-01”, the default for NUMBERS and DATES is to add DAYS. So the answer will be “1900-01-01” + whatever the resulting number is in days. Then it takes the MONTH from that result. In this case SELECT MONTH(200/18), CAST((200/18) AS DATETIME) Is one day + “1900-01-01” which is still the month of January"
Just a reminder self taught only 2 years take it easy.. :w00t:

Heh... it's ok. Self taught and only about 20 years and still learning something new just about every day. 😀

I'd say you did fine but should probably have explained both set of numbers. Mine could also be a bit misleading because of where I talked about when datetime functions see integers and someone might think that only integers will be so converted. As you point out, any number that can be converted to a datetime will actually work and not just integers.

Hope you win the gift card.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)