Query regarding last days of month [Resolved]

Posted by Sriharim under Sql Server on 5/28/2015 | Points: 10 | Views : 773 | Status : [Member] | Replies : 2
1st select statement gives last 3 days of a month. 2nd select statement gives last 2 days of a month. 3rd select statement gives last day of a month.

Look the difference, in below 3 select statement, number of days change (-1,-2,-3) is same for 3 select statements and there is change in only a month for 3 select statements .

When compare the results of 2nd query of select 1 , 2nd query of select 2 and
compare the results of 3rd query of select 1 , 3rd query of select 2, 3rd of select 3

For select 1
`select DATEADD(MM,datediff(MM,0,getdate())+1,-1), DATEADD(MM,datediff(MM,0,getdate())+1,-2), DATEADD(MM,datediff(MM,0,getdate())+1,-3)`

Result is:
` 2015-05-31 00:00:00.000     2015-05-30 00:00:00.000	   2015-05-29 00:00:00.000`

For select 2
` select DATEADD(MM,datediff(MM,0,getdate()),-1), DATEADD(MM,datediff(MM,0,getdate()),-2), DATEADD(MM,datediff(MM,0,getdate()),-3)`

Result is:
` 2015-04-30 00:00:00.000    2015-04-30 00:00:00.000   2015-04-29 00:00:00.000`

For select 3
`select DATEADD(MM,datediff(MM,0,getdate())-2,-1), DATEADD(MM,datediff(MM,0,getdate())-2,-2), DATEADD(MM,datediff(MM,0,getdate())-2,-3) `

Result is :
`2015-02-28 00:00:00.000    2015-02-28 00:00:00.000	2015-02-28 00:00:00.000`

I got same 3 output's for select 3 and same 1st 2 output's for select 2,

please tell me, why same output for select 3 ? (though i have changed number of days like 1 select statement)

 0 First of all, the DATEADD( datepart, interger, Date ) contains the date type for 3rd argument... Here, DATEADD(MM,datediff(MM,0,getdate())-2,-1) means that you are passing integer for 3rd argument rather than date type. Whenever you pass integer instead of date , the implicit conversion takes place...SQL Server treats 0 ( zero) as '1900-01-01 00:00:00.000' ; dateValue-1 means that subtracting specified number of days from a default date ( that means 1899-12-31 00:00:00.000 )run below queries to prove above statement`SELECT CAST( 0 as datetime) -- 1900-01-01 00:00:00.000SELECT CAST( 0 as datetime)-1 -- 1899-12-31 00:00:00.000` Secondly, observe the below DATEADD function with MONTH calculation..`SELECT DATEADD(MONTH, 1, '2015-01-28') -- 2015-02-28 00:00:00.000SELECT DATEADD(MONTH, 1, '2015-01-29') -- 2015-02-28 00:00:00.000SELECT DATEADD(MONTH, 1, '2015-01-30') -- 2015-02-28 00:00:00.000SELECT DATEADD(MONTH, 1, '2015-01-31') -- 2015-02-28 00:00:00.000SELECT DATEADD(MONTH, -1, '2015-03-30') -- 2015-02-28 00:00:00.000SELECT DATEADD(MONTH, -1, '2015-03-31') -- 2015-02-28 00:00:00.000` whenever the adding/subtracting months from a date and if the resultant date is exceeding the last day of month, the resultant date will be truncated to last day of month..For example, adding 1 month to 2015-01-30 date becomes 2015-02-30, but the realistic last date of February month always be 28 or 29 (depends on leap year). Hence, 2015-02-30 is invalid date. so SQL Server truncates the exceeding days from the result.... Finally results 2015-02-28 ....Because of the above two reasons, the output of your queries are same even if you change the number of days for subtraction and doing month calculations..Revert me back if you are not very clear from the above given explanationMark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gifSriharim, if this helps please login to Mark As Answer. | Alert Moderator