Dating Responsibly
Dating Responsibly
 

This is a collection of resources involving date/datetime handling in SQL Server. Or, "why is Aaron telling me to use or avoid <x>?"

BETWEEN

BETWEEN shouldn't be used for date range queries, period. I explain why:

Regional formats

Even yyyy-mm-dd is unsafe (see this db<>fiddle). Many more details:

Shorthand

@DateTimeVar + 1 is fine, but @DateVar + 1 is not. More importantly, datepart abbreviations like W and Y are downright dangerous:

Trimming time and other calculations

People resolve dates in lots of bad ways; DATEFROMPARTS and CONVERT are almost always the best approach:

Calendar table

More often than not, people are opposed to a calendar table, even though they take up very little space and solve so many problems:

Best Practices

And finally, a summary of several of the individual points raised above:

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.