A better way? Have date in query always use a date in the current year without maintenance

Question

SELECT Date_Received, DateAdd(Year, DateDiff(year, Cast('3/01/2010 12:00:00AM' as DateTime) , 
    GetDate())-1, Cast('3/01/2010 12:00:00AM' as DateTime)) as minimum_date
FROM [Volunteers].[dbo].[Applications]
WHERE Date_received >= DateAdd(Year, DateDiff(year, Cast('3/01/2010 12:00:00AM' as DateTime),
GetDate())-1, Cast('3/01/2010 12:00:00AM' as DateTime))

In several subqueries where I need to check that a date is within an acceptable range. I need to avoid using a simple constant as I really don't want to update it or a config file each new school year.

My current solution is to enter the date into the query and use some complicated DATEADD tricks to get the current year(or previous year) into the date I am using in the comparison. The exact code is above. Is there a cleaner way for me to do this?

Thanks

Edit

The business requirement is to find applications submitted between 3/01 and 7/31.

We are running background checks and it costs us money for each check we do. Identifying applications submitted during this time period helps us determine if we should do a full, partial or no background check. I will also need to check if dates concerning the previous year.

We will be doing this every year and we need to know if they were in the current year. Maintaining the queries each year to update the dates is not something I want to do.

So I am looking for a good technique to keep the year parts of the dates relevant without having to update the query or a config file.

Answer 1

Old TSQL trick: cast the date to a string in a format that starts with the four-digit year, using substring to take the first four characters of that, cast it back to a date.

Actually, the reason that it's an old TSQL trick is that, if I recall correctly, there wasn't a year() function back then. Given that there's one now, using year( getdate() ) , as others' have answered, is probably the better answer.

Answer 2

Consider keeping a set of datetime variables help readability and maintainability. I'm not sure I've captured all your requirements, especially with reference to 'previous year'. If it's as simple as finding applications submitted between 3/01 and 7/31, then this should work. If you need to determine those that were submitted Aug 1 (last year) through Feb 28 (current year), this solution could be modified to suit.

DECLARE @Start smalldatetime, @End smalldatetime, @CurrYear char(4)
SELECT @CurrYear = YEAR(getdate())      

SELECT @Start = CAST( 'mar 1 ' + @CurrYear as smalldatetime),
        @End = CAST( 'jul 31 ' + @CurrYear  as smalldatetime)

 SELECT * 
 FROM Applications
 WHERE Date_Received
 BETWEEN @Start AND @End

Answer 3

 SELECT YEAR(GETDATE())

will give you the current year.

Answer 4

Is there any reason you cannot simply use the Year function?

Select Date_Received
        , Year(GetDate()) 
                - Year('3/01/2010 12:00:00AM') - 1 
                + Year('3/01/2010 12:00:00AM')
From [Volunteers].[dbo].[Applications]
Where Date_received >= ( Year(GetDate()) 
                        - Year('3/01/2010 12:00:00AM') - 1 
                        + Year('3/01/2080 12:00:00AM') )

Another way would be to use a common-table expression

With Years As
    (
    Select Year(GetDate()) As CurrentYear
        , Year('3/01/2010 12:00:00AM') As ParamYear
        , Year('3/01/2080 12:00:00AM') As BoundaryYear
    )
Select Date_Received
    , CurrentYear - Years.ParamYear - 1 + Years.ParamYear
From [Volunteers].[dbo].[Applications]
    Cross Join Years
Where Date_received >= ( Years.CurrentYear 
                            - Years.ParamYear - 1 + Years.BoundaryYear )

Answer 5

If you need to query by month and year a lot, you should also consider making those properties into persisted, computed fields:

ALTER TABLE dbo.Applications
    ADD DateReceivedMonth AS MONTH(Date_Received) PERSISTED

ALTER TABLE dbo.Applications
    ADD DateReceivedYear AS YEAR(Date_Received) PERSISTED

SQL Server will now extract the MONTH and YEAR part of your Date_Received and place them into two new columns. Those are persisted, e.g. stored along side with your table data. SQL Server will make sure to keep them up to date automatically, e.g. if you change Date_Received, those two new columns will be recomputed (but not on every SELECT).

Now, your queries might be a lot easier:

SELECT (list of fields)
FROM dbo.Applications
WHERE DateReceivedYear = 2010 AND DateReceivedMonth BETWEEN 3 AND 7

Since these are persisted fields, you can even put an index on them to speed up queries against them!