Filtering records by today’s date sounds simple enough, but there’s a gotcha that’s easy to miss if you’re not careful. This article starts with the correct approach, then explains why the obvious method fails. So you’ll know what to do and why.
dates
SQL Server CONVERT() Date Style Codes Explained (With Examples)
In SQL Server, dates are stored in one format but often need to be displayed in another. That’s where the CONVERT() function can help. It lets you take a date value and turn it into a string in whatever format you need, whether that’s MM/DD/YYYY for an American audience or DD-MM-YYYY for a universally recognized date format.
The tricky part when using this function is the style code. This a number you pass into CONVERT() that tells it which format to use. There are dozens of style codes, and they’re not exactly intuitive to memorise. This article breaks down the most useful ones with real examples so you can find what you need and move on.
How to Convert a Date to a String in SQL Server
There are a few reasons you might need to convert a date to a string in SQL Server. Maybe you need a date in a specific format for a report. Maybe you’re concatenating it with other text. Maybe an external system expects dates as strings. Whatever the reason, SQL Server gives you several ways to do it, and the right one depends on what you’re trying to achieve.
This article covers four functions: FORMAT(), CONVERT(), CAST(), and STR().
Fix Error 155 “‘DAYS’ is not a recognized dateadd option” in SQL Server
If you’re getting an error that reads something like “‘DAYS’ is not a recognized dateadd option” in SQL Server, it’s because you’re using the DATEADD() function with an invalid datepart argument.
This often happens when you use a plural form of the argument. For example, DAYS instead of DAY. Or HOURS instead of HOUR.
The easiest way to fix this is to provide a valid datepart argument.
How to Get the Current Date in SQL Server
SQL Server has several functions that return the current date and time. If you just need today’s date for a query, that sounds like it should be simple. And it is. But there are six different functions to choose from, and they don’t all return the same thing. This article explains what each one does and when to use it.
Fix “The datepart … is not supported by date function dateadd for data type date” in SQL Server
If you’re getting error 9810 that reads something like “The datepart hour is not supported by date function dateadd for data type date“, it’s because the datepart that you’re trying to add or subtract a datepart is not supported for the data type of the original value.
This typically happens when you try to add a timepart to a date value. For example, trying to add an hour to a date value will result in this error, because the date type doesn’t support the hour datepart. You can’t have a date value that includes the hour.
How to Format Dates in SQL Server (A Beginner’s Guide)
Dates in SQL Server can be surprisingly tricky if you’re new to the game. The way dates are stored is not always the way you want them displayed, and figuring out how to convert one to the other is one of those things every beginner eventually Googles. So let’s walk through it clearly.
Using Multiple Window Calculations with DATEDIFF() in SQL Server
SQL Server’s window functions allow you to perform calculations across sets of rows that are related to the current row, without collapsing those rows into a single result like traditional GROUP BY aggregates would. When combined with the DATEDIFF() function, they provide a great way to analyze temporal patterns in your data.
Window functions can be especially useful when you need to perform multiple different calculations across the same dataset. Instead of writing separate subqueries or self-joins for each calculation, you can combine multiple window expressions in a single query. This approach is cleaner, more maintainable, and often more performant than traditional alternatives.
Using Subqueries with SQL Server’s DATEDIFF() Function
While SQL Server’s DATEDIFF() function is relatively straightforward when you’re comparing two known dates, it becomes more flexible when the dates you compare are sourced directly from your tables. Instead of hardcoding dates, you can embed subqueries directly into the DATEDIFF() function to dynamically retrieve the dates you need.
This approach can be especially useful when you’re working with aggregate functions like MIN() and MAX(), or when you need to pull specific dates based on certain conditions. The subqueries execute first, return their date values, and DATEDIFF() uses those results to perform the calculation.