6

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?

Jason Plank
  • 2,336
  • 5
  • 31
  • 40
scetoaux
  • 398
  • 7
  • 19

3 Answers3

11

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)
Jørn Schou-Rode
  • 37,718
  • 15
  • 88
  • 122
Raj More
  • 47,048
  • 33
  • 131
  • 198
  • 5
    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. Oct 06 '09 at 15:32
  • This answer produces the WRONG result. – JamesM Oct 07 '09 at 10:28
5

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
  • 44,143
  • 8
  • 72
  • 99
  • 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 Oct 06 '09 at 17:23
  • 1
    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 Oct 06 '09 at 17:50
  • @CraigTP: I upvoted you myself because you had the right answer. I have changed mine to reflect the correction – Raj More Oct 07 '09 at 13:08
  • @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 Oct 07 '09 at 13:40
0
SELECT DATEADD(year, DATEDIFF(year, 365, GETDATE()), 0)
LukeH
  • 263,068
  • 57
  • 365
  • 409
  • 1
    SELECT DATEADD(year, DATEDIFF(year, 365, GETDATE()), 0) Not work with leap year:( –  Aug 02 '11 at 07:32