Query regarding last days of month [Resolved]

Posted by Sriharim under Sql Server on 5/28/2015 | Points: 10 | Views : 1787 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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)

Mark as Answer if its helpful to you
---
Srihari

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 5/28/2015 [Member] [MVP] Platinum | Points: 50

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.000 SELECT 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.000 SELECT DATEADD(MONTH, 1, '2015-01-29') -- 2015-02-28 00:00:00.000 SELECT DATEADD(MONTH, 1, '2015-01-30') -- 2015-02-28 00:00:00.000 SELECT DATEADD(MONTH, 1, '2015-01-31') -- 2015-02-28 00:00:00.000 SELECT DATEADD(MONTH, -1, '2015-03-30') -- 2015-02-28 00:00:00.000 SELECT 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 explanation Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

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.000
SELECT 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.000
SELECT DATEADD(MONTH, 1, '2015-01-29') -- 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, '2015-01-30') -- 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, '2015-01-31') -- 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, -1, '2015-03-30') -- 2015-02-28 00:00:00.000
SELECT 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 explanation

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 5/28/2015 [Member] Starter | Points: 25

0	got the point.. very much thankful to you --- please tell me the query to get the details of last 5 days (irrespective of the day query is run) Mark as Answer if its helpful to you --- Srihari Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts