The datepart millisecond is not supported by date function dateadd for data type date.

by

Recently, I was executing a select query to check if date is today in SQL Server for one of the requirements. In this article, I will discuss the cause and solution to fix this error.

The datepart millisecond is not supported by date function dateadd for data type date.

I was executing the following SQL query.

SELECT * 
FROM Orders
WHERE Order_Date BETWEEN 
    CAST(GETDATE() AS date) AND 
    DATEADD(millisecond, -3, DATEADD(day, 1, CAST(GETDATE() AS DATE)));

After executing the above query, I got the expected output as shown in the screenshot below.

The datepart millisecond is not supported by date function dateadd for data type date.

Cause of this error

The error occurs because I am trying to add milliseconds to a DATE data type that doesn’t contain any time information.

Solution

To fix this error, we can use the following approaches.

Solution 1: Convert the DATE to a DATETIME or DATETIME2

The most straightforward approach is to convert your DATE to a data type that supports time components i.e, DATETIME or DATETIME2, as mentioned below.

SELECT * 
FROM Orders
WHERE Order_Date BETWEEN 
    CAST(GETDATE() AS date) AND 
    DATEADD(millisecond, -3, DATEADD(day, 1, CAST(GETDATE() AS DATETIME)));

This time, after executing the above query, I got the expected output without any error as shown in the screenshot below.

The datepart millisecond is not supported by date function dateadd for data type date

Solution 2: Change Your Table Schema

For a long-term solution, especially in applications where you need to track time components, modify your table schema:

-- Instead of using DATE
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE  -- Can't store time information
);

-- Use DATETIME2
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATETIME2  -- Can store date and time with high precision
);
The datepart millisecond is not supported
SELECT * 
FROM OrdersN
WHERE Order_Date BETWEEN 
    CAST(GETDATE() AS date) AND 
    DATEADD(millisecond, -3, DATEADD(day, 1, CAST(GETDATE() AS datetime2)));

After executing the above query, I got the result successfully without any error as shown in the screenshot below.

The datepart millisecond is not supported by date function

Video Tutorial

Conclusion

The error “The datepart millisecond is not supported by date function dateadd for data type date” is a reminder of the importance of choosing the right data types for your specific needs. When working with SQL Server’s date and time functions, it is essential to understand the capabilities and limitations of each data type.

Remember these key points:

  • DATE data type stores only the date with no time component
  • For time-related operations, convert to DATETIME2 or another appropriate type
  • Different data types support different dateparts
  • Always choose the correct data type based on your precision and range requirements

By following this information and solution, you’ll avoid this standard error and build more robust database applications.

You may also like the following articles.