This question was put forward during one of the project interview that my friend had appeared for

The question was related to DATEADD() function

What will be the Output for

To answer this question one must know that when

gets converted to

SELECT DAY('1900-01-01') AS DAY,YEAR('1900-01-01') AS YEAR,MONTH('1900-01-01') AS MONTH

so the output would be

Now, what will be the output for

SELECT DAY(1) AS DAY,YEAR(-1) AS YEAR,MONTH(32) AS MONTH

When we specify any (integer) value other than 0 where date should have been specified the following operation is performed

As we already know when 0 (zero) is specified instead of date then default is '1900-01-01'. But in our case we have specified 1 (one) in DAY(1) so this results in

DAY ('1900-01-01' + 1) i.e. '1900-01-02'

OR

DAY ( DATEADD ( DAY,1,'1900-01-01' ) )= DAY ('1900-01-02' ) = 2

Similarly,

YEAR(-1) = YEAR ('1900-01-01' - 1)

OR

YEAR(-1) = YEAR ( DATEADD ( DAY,-1,'1900-01-01' ) ) = YEAR ('1899-12-31') = 1899

MONTH(32) = MONTH('1900-01-01' + 32)

ORMONTH(32) = MONTH( DATEADD ( DAY,32,'1900-01-01' ) ) = MONTH('1900-02-02')= 2

So the output for

Now, it is your turn to find the output for the following SELECT statements. This will help you in understanding concept further.

SELECT DATEADD(YEAR,1,DATEADD(MONTH,3,DATEADD(DAY,32,0)))SELECT DATEADD(YEAR,-3,1)SELECT DATEADD(DAY,3,DATEADD(DAY,-1,DATEADD(DAY,-4,2)))

The question was related to DATEADD() function

What will be the Output for

`SELECT DAY(0) AS DAY,YEAR(0) AS YEAR,MONTH(0) AS MONTH`

? Will it throw an error ? If Yes, then what would be error ? If No, then what would be the output and why ?To answer this question one must know that when

**0 (zero)**is passed instead of some date to the date functions then by default that**0 (zero)**is replaced by**'1900-01-01'**. This means that error won't be thrown and the SELECT statement will yield to some result`SELECT DAY(0) AS DAY,YEAR(0) AS YEAR,MONTH(0) AS MONTH`

gets converted to

SELECT DAY('1900-01-01') AS DAY,YEAR('1900-01-01') AS YEAR,MONTH('1900-01-01') AS MONTH

so the output would be

Now, what will be the output for

SELECT DAY(1) AS DAY,YEAR(-1) AS YEAR,MONTH(32) AS MONTH

When we specify any (integer) value other than 0 where date should have been specified the following operation is performed

As we already know when 0 (zero) is specified instead of date then default is '1900-01-01'. But in our case we have specified 1 (one) in DAY(1) so this results in

DAY ('1900-01-01' + 1) i.e. '1900-01-02'

OR

DAY ( DATEADD ( DAY,1,'1900-01-01' ) )= DAY ('1900-01-02' ) = 2

*to cut story short - The non zero integer values gets added as the no. of days to the default date (1900-01-01).*Similarly,

YEAR(-1) = YEAR ('1900-01-01' - 1)

OR

YEAR(-1) = YEAR ( DATEADD ( DAY,-1,'1900-01-01' ) ) = YEAR ('1899-12-31') = 1899

MONTH(32) = MONTH('1900-01-01' + 32)

ORMONTH(32) = MONTH( DATEADD ( DAY,32,'1900-01-01' ) ) = MONTH('1900-02-02')= 2

So the output for

**SELECT DAY(1) AS DAY,YEAR(-1) AS YEAR,MONTH(32) AS MONTH**isNow, it is your turn to find the output for the following SELECT statements. This will help you in understanding concept further.

SELECT DATEADD(YEAR,1,DATEADD(MONTH,3,DATEADD(DAY,32,0)))SELECT DATEADD(YEAR,-3,1)SELECT DATEADD(DAY,3,DATEADD(DAY,-1,DATEADD(DAY,-4,2)))

Good one

ReplyDelete