How can I get the date of the first second of the year with SQL?
sql, sql-server, t-sql
Solution
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!)
Problem
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?