CURRENT DATE - 1year - 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

General SQL Server Forums

New to SQL Server Programming

CURRENT DATE - 1year

Author

Topic

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 14:20:49

i need to get jan 01,2009 current year 2009

SELECT dateadd(year,datediff(year,0,getdate()),0)

gives
2008-01-01 00:00:00.000

I need

2009-01-01 00:00:00.000

and also

SELECT dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())-1,0))

2008-08-31 23:59:59.000

i need

2009-08-31 23:59:59.000

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-01 : 14:22:54

quote:
Originally posted by Chinni

i need to get jan 01,2009 current year 2009

SELECT dateadd(year,datediff(year,0,getdate())+1,0)

gives
2008-01-01 00:00:00.000

I need

2009-01-01 00:00:00.000

Thanks

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 14:23:43

2009 year is not fixed it shd be from getdate()

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 14:30:22

It is still 2008.

SELECT dateadd(year,datediff(year,0,getdate()),0)
SELECT dateadd(year,datediff(year,-1,getdate()),0)

E 12°55'05.63"
N 56°04'39.26"

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 14:35:47

SELECT dateadd(year,datediff(year,-1,getdate()),0)

this worked -- thanks

SELECT dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())-1,0))
2008-08-31 23:59:59.000

how to change to 2009 like before query

desired result
2009-08-31 23:59:59.000

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-01 : 19:58:35

quote:
Originally posted by Chinni

SELECT dateadd(year,datediff(year,-1,getdate()),0)

this worked -- thanks

SELECT dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())-1,0))
2008-08-31 23:59:59.000

how to change to 2009 like before query

desired result
2009-08-31 23:59:59.000

You should add the "+1" like in the other query.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 00:43:04

Why are you so hung up on 23:59:59?

It's more reliable to have an open-ended WHERE clause, like this

SELECT *
FROM Table1
WHERE Col1 >= '20080101' AND Col1 < '20090101'

of you want to search for all records during the year of 2008.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources