How to Format SQL Server Time Values as HH:MM:SS?

Formatting SQL Server Time Values as HH:MM:SS

This article explains how to correctly format SQL Server time values as HH:MM:SS, addressing a common misconception about time data type storage.

SQL Server doesn't store time with a specific display format. The underlying storage is format-agnostic. This applies to all date and time types (Date, DateTimeOffset, DateTime2, SmallDateTime, DateTime, and Time). Therefore, simply casting to TIME won't guarantee the HH:MM:SS format.

To achieve the desired HH:MM:SS output, you need to convert the TIME value to a character string. Use the following CONVERT function:

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

This converts the TIME value to a character string of length 8 (HH:MM:SS) using style 108. Note the change from char(10) to char(8) to accurately reflect the length of HH:MM:SS.

For more detailed information, consult these resources:

• Microsoft Documentation: CAST and CONVERT Functions
• Stack Overflow: Casting SQL Server TIME to hh:mm:ss
• SQL Server Internals: Date and Time

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