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

Question

I am working on a cleanup procedure on SQL Server 2005 that should delete all rows in a table older than 1 year ago + time elapsed in the current year.

Example: if I perform the procedure today from 6-10-2009 , it should delete lines older than 2008-01-01 00:00 (including 2007 and back).

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

I tried this:

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

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

Can someone help me please?

Answer 1

This will work:

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

( , "" , , , , , , , !)

Answer 2

EDIT: , . , .

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

:

select DATEADD(yy, DATEADD(yy, DATEDIFF(yy,0,@YourDateTimeValue), 0), -1)

Answer 3

SELECT DATEADD(year, DATEDIFF(year, 365, GETDATE()), 0)