Search code examples
sql-servert-sqldatedateadd

last occurence of date

I need to find the last occurence of a date (in my case a static date 1st of may)

I made this which works but i know this can be done in a much smarter way

declare @lastmay date
set @lastmay = DATEADD(YY,YEAR(GETDATE())-2000,'20000501')
IF @lastmay <= GETDATE()
BEGIN
    SET @lastmay = DATEADD(YY,-1,@lastmay)
END
Solution

  • When you are working with dates in SQL it can be a real help to have a Dates utility table on your database that you can then compare against.

    This article discusses it well: http://www.techrepublic.com/blog/datacenter/simplify-sql-server-2005-queries-with-a-dates-table/326

    If you implemented that your queries could become quite simple such as (using columns from article)

    Declare @lastmay date

Select @lastmay = DateFull 
from DateLookup
    where MonthNumber = 5
    and MonthDay = 1
    and Datefull < getdate()