date function in sql 2008 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

date function in sql 2008

Author

Topic

anilr499
Starting Member

18 Posts

Posted - 2012-05-02 : 23:02:37

declare @date datetime set @date ='12/31/2011'
declare @dateTo datetime set @dateTo = dateadd(m,-1,@date)
declare @dateko datetime set @dateko=DATEADD(m,1,@date)
declare @datepo datetime set @datepo = dateadd(m,-2,@date)
(SELECT MONTH([END]) as mth, YEAR([END]) as yr, COUNT(Task) AS DUE FROM xTask where [END]<= @date and [end]>@dateTo and status <> 'c'
group by MONTH([END]), YEAR([END]))union
(SELECT MONTH([END]) as mth, YEAR([END]) as yr, COUNT(Task) AS DUE FROM xTask where [END]<= @dateTo and [end]>@datepo and
status <> 'c' group by MONTH([END]), YEAR([END])) union
(SELECT MONTH([END]) as mth, YEAR([END]) as yr, COUNT(Task) AS DUE FROM xTask
where [END]<= @dateko and [end] >@date and status <> 'c' group by MONTH([END]), YEAR([END]))

input :@date='12/31/2011'

output should be:

@dateto='11/30/2011'
@dateko='01/31/2012'
@datepo=''10/31/2011'

but my output is : (exactly substracting 30 days i think)

@dateto='12/01/2011'
@dateko='01/30/2012'
@datepo='11/01/2011'

can some body help me how to get the previous month irrespective of day in month..

thanks....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 15:22:55

you just need this i guess


declare @date datetime set @date ='12/31/2011'

SELECT MONTH([END]) as mth, YEAR([END]) as yr, COUNT(Task) AS DUE 
FROM xTask 
where [END]>= dateadd(mm,datediff(mm,0,@date)-2,0) 
and  [END]< dateadd(mm,datediff(mm,0,@date)+1,0) 
and status <> 'c' 
group by MONTH([END]), YEAR([END]))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-04 : 06:22:02

Also make sure to use unamibigious date formats
http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources