What's the best way to perform math on a floored date in SQL Server?

Question

This is related to Floor a date in SQL server, but I'm taking it one step further:

I'm writing a query on a SQL Server table and I want to get all records for the current calendar year and the previous calendar year. So, right now, I'd want a query to return all records between January 1st, 2008 and today. But come January 1st, 2010, I want it to return records no older than 2009.

Essentially, I want to floor the current date to the beginning of the year and then subtract 1.

After rummaging through some SQL Server documentation, I came up with this:

   WHERE create_date >= CAST((CAST(YEAR(GETDATE()) AS int) -1) AS varchar)

but it feels kind of ugly. Is there a better way?

Answer 1

Why not just use the year function on create_date as well?

WHERE YEAR(create_date) >= (YEAR(GETDATE()) -1)

This assumes (as you did) that there are no records in the database greater than today's date

Answer 2

I would suggest assigning a variable with the date lastyear-01-01, either by making an UDF for it, or something like

DECLARE @startOfLastYear DATETIME
SET @startOfLastYear = CAST(YEAR(GETDATE()) - 1 AS VARCHAR) + '-01-01'

Then do the query:

WHERE  create_date >= @startOfLastYear

Because of two reasons:

1. Using YEAR() or any other function on data from tables (i.e. YEAR(create_date)) makes indices unusable and decreases the performance of the query
2. The variable name tells exactly what it is, and reading the code is easier.