How to Remove Milliseconds from Datetime in SQL Server

Disclaimer/Disclosure: Some of the content was synthetically produced using various Generative AI (artificial intelligence) tools; so, there may be inaccuracies or misleading information present in the video. Please consider this before relying on the content to make any decisions or take any actions etc. If you still have any concerns, please feel free to write them in a comment. Thank you.
---

Summary: Learn efficient methods to remove milliseconds from datetime in SQL Server using common SQL functions and queries.
---

How to Remove Milliseconds from Datetime in SQL Server

Managing date and time data in SQL Server often requires manipulating the datetime format to fit specific needs. One common requirement is to remove the milliseconds from a datetime value. Here, we'll explore a few methods to achieve this in SQL Server.

Using CONVERT with Style 120

One of the simplest ways to remove milliseconds from a datetime value in SQL Server is by using the CONVERT function with style 120. This method not only removes milliseconds but formats the result as a string.

[[See Video to Reveal this Text or Code Snippet]]

In this example, the milliseconds portion .123 is omitted, leaving us with 2023-09-15 13:45:26.

Using CAST and CONVERT for Datetime

If you prefer to keep the result as a datetime data type, you can combine CAST with CONVERT.

[[See Video to Reveal this Text or Code Snippet]]

This method converts the datetime value to a formatted string without milliseconds and then casts it back to a datetime data type.

Using Date Functions

Another approach is to use date functions like DATEADD and DATEDIFF to exclude the milliseconds part.

[[See Video to Reveal this Text or Code Snippet]]

Here, DATEDIFF calculates the difference in seconds from a base date (1900-01-01), effectively removing the milliseconds. The DATEADD function then adds the difference back to the base date, yielding a datetime value without milliseconds.

Conclusion

Removing milliseconds from a datetime in SQL Server can be achieved through various methods, each with its own advantages. Whether you choose to use CONVERT for a straightforward string conversion or prefer more granular control with date functions, SQL Server offers flexible options to meet your needs.

Employ the method that best suits your particular use case and database design to ensure optimal performance and accuracy.