Recently, I worked on a requirement to retrieve the list of Sales order dates for specific time components. I used the DATEPART function, but after executing the query, I got this error.
The datepart hour is not supported by date function dateadd for data type date.
I executed the SQL query below to retrieve the Sales table record list.
Select * from sales
WHERE DATEPART(HOUR, sale_Date) BETWEEN 9 AND 16But, after executing the above query, I got the above error as shown in the screenshot below.
Solution
To fix this issue, we need to do a double cast after getting the current time using the following query.
Select * from sales
WHERE DATEPART(HOUR, cast(cast(sale_Date as date) as datetime)) BETWEEN 9 AND 16After executing the above query, I got the expected output without any error as shown in the screenshot below.
Video Tutorial
You may also like the following articles.
- ‘to_date’ is not a recognized built-in function name.
- ‘EXTRACT’ is not a recognized built-in function name.
- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
- The datepart millisecond is not supported by date function dateadd for data type date.
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.