views:

702

answers:

3

Hello,

I'm working on a purging procedure on SQL Server 2005 which has to delete all rows in a table older than 1 year ago + time passed the current year.

Ex: If I execute the procedure today 6-10-2009 it has to delete rows older than 2008-01-01 00:00 (that is 2007 included and backwards).

How can I get the date of the first second of the year?

I've tried this:

select cast((DATEPART(year, getdate()) -1 )AS DATETIME);

but I get 1905-07-02 00:00:00.000 and not 2008-01-01 00:00 ( as I wrongly expected).

Can someone help me, please?

Thanks in advance.

+7  A: 

EDIT: This was returning current year, when the question was for previous year. Code has been corrected to reflect this.

use this

select DATEADD(yy, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0), -1)

OR to use your variable:

select DATEADD(yy, select DATEADD(yy, DATEDIFF(yy,0,@YourDateTimeValue), 0), -1)
Raj More
this is a fairly standard thing in SQL Server that everyone should learn, here is a link that shows how to do it for year, month, day, hour, minute... http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server
KM
This answer produces the WRONG result.
JamesM
+2  A: 

This will work:

select cast('01 jan' + CAST((DATEPART(year, getdate())-1) as varchar) AS DATETIME);

(I know it's not the "best" solution and probably involves more casts than necessary, but it works, and for how this will be used it seems to be a pragmatic solution!)

CraigTP
Hmm.. Interesting how the other answer to this question (at the time of writing) has 8 upvotes, whilst mine has only 1 up and 1 down vote, yet mine is the only solution that gives the correct answer. (ie. For today's date (6 Oct 2009) the result is 2008-01-01 00:00:00, whilst Raj's solution returns 2009-01-01 00:00:00)
CraigTP
I like SELECT CONVERT(DATETIME,CAST(YEAR(GETDATE())-1 AS VARCHAR) + '0101',112) or SELECT CONVERT(DATETIME,CAST(YEAR(GETDATE())-1 AS VARCHAR),112) more, because it specifies input format.
LukLed
@CraigTP: I upvoted you myself because you had the right answer. I have changed mine to reflect the correction
Raj More
@Raj - Thanks. I do prefer the DateAdd/DateDiff method that your solution uses, although I was amazed at the upvoters who clearly didn't run your original SQL or check the result!
CraigTP
A: 
SELECT DATEADD(year, DATEDIFF(year, 365, GETDATE()), 0)
LukeH