Monday 18 May 2015

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(0AS DAY,YEAR(0AS YEAR,MONTH(0AS 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(0AS DAY,YEAR(0AS YEAR,MONTH(0AS 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(1AS DAY,YEAR(-1AS YEAR,MONTH(32AS 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(32MONTH('1900-01-01' 32)
OR
MONTH(32MONTHDATEADD  DAY,32,'1900-01-01' ) ) MONTH('1900-02-02')2

So the output for SELECT DAY(1AS DAY,YEAR(-1AS YEAR,MONTH(32AS 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)))

1 comment:

bloggerwidgets