Question on the date add function

Question

Question on the date add function

itmasterw 60042

SSCrazy

Points: 2858
More actions
May 26, 2015 at 8:59 am

#297242

Hi,
I saw the following and was woundering how it works? That is what does the -1900,0 do?
dateadd(year,[Year]-1900,0)
Thank you

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

itmasterw 60042 (5/26/2015)
Hi,
I saw the following and was woundering how it works? That is what does the -1900,0 do?
dateadd(year,[Year]-1900,0)
Thank you

A little ambiguous. What is the data stored in the [Year] column?

A guess, if [Year] has the value 2015, it should return 2015-01-01.

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 2

The Year holds 2015

and if I run this:

Select dateadd(year,[Year]-1900,0)
from ##Shift

I get this (if it helps)

Select dateadd(year,[Year]-1900,0)

from ##Shift

Column1

2015-01-01

But I still do not under stand what -1900,0 does?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

itmasterw 60042 (5/26/2015)
The Year holds 2015
and if I run this:
Select dateadd(year,[Year]-1900,0)
from ##Shift
I get this (if it helps)
Select dateadd(year,[Year]-1900,0)
from ##Shift
Column1
2015-01-01
2015-01-01
2015-01-01
But I still do not under stand what -1900,0 does?

First, 1900-01-01 is considered the zero day. If you run cast(0 as datetime) it will return 1900-01-01. If you subtract 1900 from 2015, that is the number years from 1900 to 2015. If you then add that integer value to 0 using dateadd(YEAR,[Year] - 1900,0), it returns 2015-01-01 00:00:00.

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 4

That makes it clearer, thanks for the explanation.