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

Converting Integers to Time Format in SQL Server 2008

Question:

In SQL Server 2008, I need to convert an integer representing time into the HH:MM:SS::00 format. Additionally, I want clarification on whether the '00' in the format signifies milliseconds.

Answer:

To convert an integer time into the desired format, you can use the following steps:

1. Extract Hours: Divide the integer by 1000000 and take the remainder divided by 100 to get the number of hours.
2. Extract Minutes: Divide the integer by 10000 and take the remainder divided by 100 to get the number of minutes.
3. Extract Seconds: Divide the integer by 100 and take the remainder divided by 100 to get the number of seconds.
4. Extract Milliseconds: Multiply the remainder of the integer divided by 100 by 10.

The '00' in the format does indeed represent milliseconds.

Examples and Results:

Example 1:

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

Example 2:

declare @T int = 421151;

select dateadd(hour, (@T / 1000000) % 100,
       dateadd(minute, (@T / 10000) % 100,
       dateadd(second, (@T / 100) % 100,
       dateadd(millisecond, (@T % 100) * 10, cast('00:00:00' as time(2))))));

Result:

10:45:58.36

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