get the first and last month of the year

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

General SQL Server Forums

New to SQL Server Programming

get the first and last month of the year

Author

Topic

shemayb
Posting Yak Master

159 Posts

Posted - 2007-12-19 : 18:45:35

hi! i have this following query:Select DATEADD(YEAR, -2, @sDate),(dateadd(dd,-(day(dateadd(yy,1,@sDate))),dateadd(yy,1,@sDate)))..the results of these are:2005-01-01 ,2007-12-31 but i want 2007-12-31 to be 2005-12-31..how can i do that using dateadd function?

Funnyfrog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-19 : 18:49:25

Subtract off 2 years using DATEADD.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shemayb
Posting Yak Master

159 Posts

Posted - 2007-12-19 : 18:51:35

i will subract this one (dateadd(dd,-(day(dateadd(yy,1,@sDate))),dateadd(yy,1,@sDate))) by 2?

Funnyfrog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 18:52:37

[code]SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19021231', CURRENT_TIMESTAMP), '19000101'),
DATEADD(YEAR, DATEDIFF(YEAR, '19021231', CURRENT_TIMESTAMP), '19001231')[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 19:02:50

For more information about DATEDIFF function works, see
http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 01:47:47

quote:
Originally posted by shemayb

hi! i have this following query:Select DATEADD(YEAR, -2, @sDate),(dateadd(dd,-(day(dateadd(yy,1,@sDate))),dateadd(yy,1,@sDate)))..the results of these are:2005-01-01 ,2007-12-31 but i want 2007-12-31 to be 2005-12-31..how can i do that using dateadd function?

Funnyfrog

Instead of (dateadd(dd,-(day(dateadd(yy,1,@sDate))),dateadd(yy,1,@sDate)))
use
DATEADD(DAY,-1,DATEADD(YEAR,1,DATEADD(YEAR, -2, @sDate)))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources