Dating Responsibly
Collection of resources involving date/datetime handling in SQL Server. Or, "why is Aaron telling me to use or avoid <x>?"
BETWEEN
- BETWEEN should never be used for date range queries (and especially shouldn't be combined with EOMONTH). I explain why:
- What do BETWEEN and the devil have in common?
- Video : Using BETWEEN for date range queries
- Will you use EOMONTH?
Regional formats
- Even yyyy-mm-dd is unsafe (see this db<>fiddle). Many more details:
- What date/time literal formats are LANGUAGE and DATEFORMAT safe?
- How SQL Server handles the date format YYYY-MM-DD
- Video : Regional datetime formats
Shorthand
- Bad Habits to Kick : Using shorthand with date/time operations
- Video : Datetime shorthand (and other tricks)
@DateTimeVar + 1 is fine, but @DateVar + 1 is not. More importantly, datepart abbreviations like W and Y are downright dangerous:
Trimming time
- People resolve dates in lots of bad ways;
- FORMAT() is nice and all, but…
- What is the most efficient way to trim time from datetime?
- Trimming time from datetime – a follow-up
- Bad Habits to Kick : Mis-handling date / range queries
- Simplify Date Period Calculations in SQL Server
- Build a cheat sheet for SQL Server date and time formats
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:
- Creating a date dimension or calendar table in SQL Server
- Using a calendar table in SQL Server – Part 1
- Calendar Table in SQL Server to Identify Date Gaps and Islands
- Create a Calendar Table in SQL Server to Optimize Scheduling Problems
Best Practices
- And finally, a summary of several of the individual points raised above:
- DateTime Best Practices