How to Convert an Integer to HH:MM:SS::00 Time Format in SQL Server 2008?

Converting Integer Time to HH:MM:SS::00 Format in SQL Server 2008

To convert an integer representation of time to the HH:MM:SS::00 format in SQL Server 2008, follow these steps:

• Extract Hour: Divide the integer value by 1000000 and take the remainder modulo 100. This result represents the hour.
• Extract Minute: Divide the previous result by 10000 and take the remainder modulo 100. This result represents the minute.
• Extract Second: Divide the previous result by 100 and take the remainder modulo 100. This result represents the second.
• Extract Millisecond: Multiply the remainder by 10. This result represents the millisecond.

Example:

To convert the integer 23421155 to time format, use the following query:

DECLARE @T INT = 23421155;

SELECT (@T / 1000000) % 100 AS Hour,
       (@T / 10000) % 100 AS Minute,
       (@T / 100) % 100 AS Second,
       (@T % 100) * 10 AS Millisecond;

Result:

Hour	Minute	Second	Millisecond
23	42	11	55

Clarification:

In the HH:MM:SS::00 format, the double colon (::) represents the decimal point. The digits following the decimal point represent the milliseconds.

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