CODEWITHSUNDEEP
sqlsql-server-2008datetime
26071986
26071986

Reputation: 2330

Get items that were created exactly one, two, n... years ago

From some table I should select items, that were created exactly at least one year ago - at the same month and day as today. So, items created at 2011-10-10, 2013-10-10 will suit, but not at 2014-10-10.

The following query works just fine, but maybe there is a more elegant way? Thanks in advance.

select * from myTable 
where 
    DATEPART(MONTH, CreatedOn) = DATEPART(MONTH, GETDATE()) -- item was created at the same month
    and DATEPART(DAY, CreatedOn) = DATEPART(DAY, GETDATE()) -- and at the same day
    and DATEDIFF(YEAR, CreatedOn, GETDATE()) >= 1           -- and at least one year ago

Upvotes: 0

Views: 121

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

I would (if possible) change the table to have an extra, computed column, defined as:

DATEADD(year,DATEDIFF(year,CreatedOn,0),CONVERT(date,CreatedOn))

Say, called AnniversaryDate. This has the effect of "normalizing" the dates into the year 1900. This also treats 29th February the same as 28th February (both get normalized to 28th).

You now create an index on this computed column, making searches within it quick and easy. You use the same formula to transform GETDATE() into a "normalized" date for searching this data. All you have to do now is exclude results that occurred this year, which should be as simple as one extra filter. So your final query wound be:

AnniversaryDate = DATEADD(year,DATEDIFF(year,GETDATE(),0),
                  CONVERT(date,GETDATE())) and
CreatedOn < CONVERT(date,GETDATE())

(I'd probably create a single index on both AnniversaryDate and CreatedOn, to maximally benefit from this query).

Upvotes: 1

Related Questions