How to Convert SQL Server DateTime to HH:MM:SS Time Format?

Convert DateTime to time in HH:MM:SS format

In SQL Server, the Time data type stores time values ​​but does not include a specific display format. To extract a time value in HH:MM:SS format, we need to convert the DateTime field to a character string.

Using the CONVERT function, we can convert the DateTime field into a string with the desired format as follows:

Instructions:

The
• CONVERT function converts the [datetime] field into a character string.
The
• varchar(8) parameter specifies the length of the result string (8 characters in this case). Using varchar is more efficient than char because varchar only stores the actual number of characters used.
• CAST([datetime] AS TIME) Converts the datetime value to the TIME data type, removing the date part.
The
• 108 style code instructs SQL Server to convert Time values ​​to HH:MM:SS format, ignoring milliseconds.

Example:

Given the DateTime value '2015-03-19 00:00:00.000', the above query will return '00:00:00'. Note that if datetime contains time information, that time information will be returned.

Technical background:

Internally, SQL Server stores the Time value as a 4-byte integer representing the number of clock ticks after midnight. Each tick represents 3.3 milliseconds. Therefore, the Time data type itself does not have a specific display format.

When converting a DateTime to a Time using CAST syntax, milliseconds are preserved in the resulting Time value. To obtain a formatted string in HH:MM:SS format, you need to use the CONVERT function. Using CAST to first convert the value to the TIME type, and then using CONVERT to format the output, ensures clearer and more accurate conversion results.

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