How Can I Cast a SQL Server DateTime Field to HH:MM:SS Time Format Without Fractional Seconds?

Convert DateTime to Time

When using CAST to convert a DateTime field to a Time, the output often contains fractional seconds or milliseconds, which may not be what you expect. To filter or convert to the exact HH:MM:SS format, you must understand the internal representation of time in SQL Server.

Time storage and format

Unlike display formats, times in SQL Server are not inherently stored in a certain format. It is an 8-byte integer, the first 4 bytes represent the date and the remaining 4 bytes represent the number of ticks since midnight. One tick represents 3.3 milliseconds.

Thus, time values ​​in SQL Server exist as integer values ​​and not in a specific format.

Convert to character format

To get the HH:MM:SS format, you need to use the CONVERT function to convert to character type:

<code class="language-sql">SELECT CONVERT(char(10), [time], 108) as CSTTime</code>

Description

• char(10) specifies a character string of length 10.
• 108 is a style code specifying the HH:MM:SS format.

This conversion process will convert the time value into a character string in the required HH:MM:SS format.

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