Skip to main content

SQL Server Convert HH:mm:ss String into Time Data Type, the Total Number of Minutes, or the Total Nmber of Seconds.

In this post, we will demonstrate the conversion of a HH:mm:ss time string into a time data type, and then it calculates and displays the total number of minutes and seconds since midnight for that converted time. It's a useful example for working with time-related calculations involving seconds in SQL Server's T-SQL.

Convert String to Time Type, Minutes, and Seconds​

You can use this Fiddle to follow along and practice converting string to time, minutes, or seconds

Convert HH:mm:ss String to Time, Minutes, and Seconds
DECLARE @timeString VARCHAR(8) = '10:32:59';
DECLARE @time TIME = CONVERT(TIME, @timeString);

SELECT @time AS Time
	,DATEDIFF(MINUTE, CAST('00:00' AS TIME), @time) AS NumOfMins
	,DATEDIFF(SECOND, CAST('00:00' AS TIME), @time) AS NumOfSeconds;

Query Result

TimeNumOfMinsNumOfSeconds
10:32:59.000000063237979

Here is an explanation of the above code snippet:

  1. DECLARE @timeString VARCHAR(8) = '10:32:59'; declares a variable named @timeString and assigns the value '10:32:59' to it. This variable now holds the input time string in the format HH:mm:ss.

  2. DECLARE @time TIME = CONVERT(TIME, @timeString); declares another variable named @time and assigns it the value obtained by converting the @timeString variable into a time data type. This step converts the input time string into a valid time value that includes hours, minutes, and seconds.

  3. SELECT @time AS TIME, DATEDIFF(MINUTE, CAST('00:00' AS TIME), @time) AS NumOfMins, DATEDIFF(SECOND, CAST('00:00' AS TIME), @time) AS NumOfSeconds; performs a SELECT query with similar columns as before:

    • The @time variable value is displayed under the column name Time, representing the time value that was converted from the input time string.
    • DATEDIFF(MINUTE, CAST('00:00' AS TIME), @time) calculates the difference in minutes between midnight ('00:00') and the value stored in the @time variable. This provides the total number of minutes since midnight.
    • DATEDIFF(SECOND, CAST('00:00' AS TIME), @time) calculates the difference in seconds between midnight ('00:00') and the value stored in the @time variable. This gives the total number of seconds since midnight.