Get dates quickly with SQL

That’s right, now you too can use T-SQL to get dates quickly! Today at the client I came across the following chunk of SQL in a where clause: dateadd(m,-24,(cast(datepart(m,getdate()) as varchar(2))+'/1/'+cast(datepart(yyyy,getdate()) as varchar(4)))) It’s used to return the first of the month two years ago. So, for today, this would return 2006-09-01 What’s wrong with this? Well, in this query there are two string concatenations, two explicit conversions from an INTEGER to a VARCHAR, and an implicit conversion from VARCHAR to DATETIME. When run across a large number of dates, this can rapidly become an expensive operation. Especially considering that this was half of a BETWEEN statement that had something similar on the other side of the AND. Is there a better way to do this? You bet! SELECT DATEADD(mm, -24, DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) This involves no implicit or explicit casting, just some basic date math. While I haven’t done any benchmarking on either option, I’m going to go out on a limb and state that the second option is much more efficient and, arguably, a lot clearer. In addition to being cleaner, using DATEADD and DATEDIFF makes no assumption about the current locale of your database. This could save you if you are dealing with databases that are spread across the globe or if you have to deal with localized tables within a database. More information can be found either from Pinal Dave or from Shane Cooper. I’ve bookmarked both of these links and, if you’ve ever had to write SQL code, you should to. N.B. If you’re using this over any more than a few records you will want to create a variable to store the result of the date math since date functions are non-deterministic. Meaning, if you perform a SELECT on 2 million records, the math will be performed at least 2 million times (4 million if you’re using a BETWEEN). Edit: Special thanks to my brother for coming to my rescue today and helping me track down an easier, SQL-based, way to do this.