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

@DateTimeVar + 1 is fine, but @DateVar + 1 is not. More importantly, datepart abbreviations like W and Y are downright dangerous:
• Bad Habits to Kick : Using shorthand with date/time operations
• Video : Datetime shorthand (and other tricks)

Trimming time and other calculations

People resolve dates in lots of bad ways; DATEFROMPARTS and CONVERT are almost always the best approach:
• 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

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