Stop Making Mistakes with Dates in SQL Server

Working with dates in SQL Server seems simple, right? You just write a quick query, filter by a range, and you're done. But after years of working with databases, I have seen many developers make the same mistakes over and over again.

In this post, I want to share some “best practices” to help you avoid common bugs and write safer, more reliable code.

1. Why You Should Stop Using BETWEEN for Date Ranges

We are often taught to use BETWEEN to find data within a range. For example: WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31'.

The problem: BETWEEN includes the end date. If your OrderDate column includes time information (like 2023-01-31 14:30:00), BETWEEN might cut off data or include data you didn't intend to.

The better way: Use >= and <.

• Good: WHERE OrderDate >= '2023-01-01' AND OrderDate < '2023-02-01'

This method is much safer because it captures everything up to the very beginning of the next month without worrying about time precision.

2. Be Careful with Date Formats

Even a common format like yyyy-mm-dd can sometimes be unsafe. Depending on your server's language settings (Locale), SQL Server might misunderstand the date format. This can lead to errors that are very hard to find.

Always try to use standard formats or specific conversion functions to ensure SQL Server interprets your dates exactly as you intended.

3. Avoid Dangerous Shortcuts

• Math with Dates: Adding 1 to a DateTime variable works differently than adding 1 to a Date variable. Don't assume the math works the same way for every data type.
• Datepart Abbreviations: Using shortcuts like W (for Week) or Y (for Year) in functions like DATEPART is dangerous. These shortcuts can change meaning depending on server settings. Always write out the full name (like week or year) to avoid confusion.

4. The Best Approaches: DATEFROMPARTS and CONVERT

When you need to create a date or convert a string into a date, avoid “lazy” methods. The functions DATEFROMPARTS and CONVERT are your best friends. They are explicit and much less likely to cause bugs.

5. Don't Fear the "Calendar Table"

Many developers dislike the idea of creating a separate "Calendar Table." They think it's a waste of space.

The truth: A Calendar Table is a simple table that lists every day, month, and year. It takes up very little space and solves almost every complicated date problem you will ever have (like finding business days, holidays, or specific fiscal quarters). It is one of the smartest tools in a database designer's kit.