SQL Server整数转TIME技术求助：需可用于SELECT的代码适配DATEDIFF()

Got it, let's solve this problem. You have integers representing time in a condensed HHmm format (like 449 = 04:49:00, 25 = 02:05:00) and need to convert them to a proper TIME data type to use with DATEDIFF(). Here are two practical solutions:

1. Direct Expression in SELECT (No Function Needed) #

If you only need this conversion once or in a single query, you can use a string-manipulation expression directly. The idea is to pad the integer with leading zeros to make it 4 digits, split into hours/minutes, then format it as a valid time string and cast it to TIME.

SELECT
  your_time_int_column,
  -- Convert integer to TIME
  CAST(
    STUFF(
      STUFF(RIGHT('0000' + CAST(your_time_int_column AS VARCHAR(4)), 4), 3, 0, ':'),
      6, 0, ':00'
    ) AS TIME
  ) AS converted_time
FROM your_table;

Breakdown of the expression: #

• RIGHT('0000' + CAST(your_time_int_column AS VARCHAR(4)), 4): Pads the integer with leading zeros to ensure it's 4 characters long (e.g., 25 → '0025', 449 → '0449').
• First STUFF(..., 3, 0, ':'): Inserts a colon after the first two characters to split hours and minutes (e.g., '0025' → '00:25').
• Second STUFF(..., 6, 0, ':00'): Adds the seconds part (":00") to make it a full time string (e.g., '00:25' → '00:25:00').
• CAST(...) AS TIME: Converts the formatted string to the TIME data type.

Using with DATEDIFF():

Here's how you can plug this into DATEDIFF() to calculate differences, for example between a converted integer time and a native TIME column:

SELECT
  DATEDIFF(
    MINUTE,
    -- Convert start time integer to TIME
    CAST(STUFF(STUFF(RIGHT('0000' + CAST(start_time_int AS VARCHAR(4)), 4), 3, 0, ':'), 6, 0, ':00') AS TIME),
    -- Existing TIME column
    end_time_column
  ) AS total_minutes_diff
FROM your_table;

2. Custom Scalar Function (For Reusability) #

If you need this conversion across multiple queries, creating a custom function will clean up your code and make it easier to maintain.

CREATE FUNCTION dbo.IntToHHmmTime(@timeInteger INT)
RETURNS TIME
AS
BEGIN
  DECLARE @formattedTimeStr VARCHAR(8);
  
  -- Pad integer to 4 digits, split into hours/minutes, add seconds
  SET @formattedTimeStr = RIGHT('0000' + CAST(@timeInteger AS VARCHAR(4)), 4);
  SET @formattedTimeStr = STUFF(@formattedTimeStr, 3, 0, ':') + ':00';
  
  -- Return as TIME type
  RETURN CAST(@formattedTimeStr AS TIME);
END;

Using the function with SELECT and DATEDIFF():

SELECT
  your_time_int_column,
  dbo.IntToHHmmTime(your_time_int_column) AS converted_time,
  -- Calculate difference between two converted integer times
  DATEDIFF(
    SECOND,
    dbo.IntToHHmmTime(start_time_int),
    dbo.IntToHHmmTime(end_time_int)
  ) AS total_seconds_diff
FROM your_table;

Important Notes: #

• Make sure your integer values are within the valid TIME range (0 to 2359). Values like 2400 will throw an error since TIME only goes up to 23:59:59.
• If your integers might include milliseconds (e.g., 44912 = 04:49:12), you can adjust the function/expression to handle that by extending the padding to 6 digits and splitting accordingly.

内容的提问来源于stack exchange，提问作者Kiran Kumar