How Can I Convert SQL Server DateTime to HH:MM:SS Time Format Without Milliseconds?

Formatting SQL Server DateTime: Eliminating Milliseconds from HH:MM:SS

Directly casting a SQL Server DateTime field to Time using CAST doesn't always guarantee an HH:MM:SS format without milliseconds. This is due to how Time is internally represented.

SQL Server stores Time as a 4-byte integer representing the number of 3.3-millisecond ticks since midnight. Therefore, the inherent value contains milliseconds.

To achieve the desired HH:MM:SS format, use the CONVERT function:

<code class="language-sql">SELECT CONVERT(VARCHAR(8), [time], 108) AS FormattedTime</code>

This converts the Time value into an 8-character string ("HH:MM:SS")—removing the milliseconds. Using VARCHAR(8) is more precise than CHAR(10) as it only allocates the necessary space.

Understanding the underlying storage of Time is key to correctly formatting and manipulating time data. The CONVERT function provides the necessary control to display the data in the desired HH:MM:SS format.

The above is the detailed content of How Can I Convert SQL Server DateTime to HH:MM:SS Time Format Without Milliseconds?. For more information, please follow other related articles on the PHP Chinese website!