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

Question

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 in 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?

Answer 1

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, DATEADD(yy, DATEDIFF(yy,0,@YourDateTimeValue), 0), -1)

Answer 2

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!)