Interview Question - What will be the Output for ....

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 

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 is

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)))