
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:
- 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 and other calculations
- People resolve dates in lots of bad ways;
- FORMAT() is nice and all, but… (also see this series: Part 1 | Part 2)
- 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
- Find the 3rd Tuesday or last non-weekday in a month
- 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