How to add leading 0's to integer in sql?-CodePudding

I have date and time values that are stored (separately) as integers. I now want to convert them into datetime data type into a new column but have trouble handling the time.

My first idea was to do it kinda like this (I can deal with the dates so in this example I'm just using a dummy date):

SET new_column = CAST(CONCAT('2020-01-01T', 
                             date_column / 10000, ':', 
                             date_column % 10000 / 100, ':', 
                             date_column % 100) AS DATETIME)

The issue is that CAST expects exactly the format 'yyyy-mm-ddThh:mm:ss' but the calculation may sometimes only return one digit instead of the needed two digits for a part of the time.

For example the time one minute after 9am would result in the string '2020-01-01T9:1:0' though '2020-01-01T09:01:00' would be required.

Is there an efficient way to add leading 0s?

EDIT: The date_column contains integer values that represent times in the format hhmmss. So for example 10am would be stored as 100000, one minute after 9am would be stored as 90100.

I know that it is stupid to encode times/dates as integer. This is for legacy reasons and my current task is to fix it ;)

CodePudding user response：

You need to pad with a zero to deal with single-digit output. Unfortunately, T-SQL doesn't have a PAD or RPAD function, so you have to do it manually:

  SET new_column = CONVERT(datetime,
    CONCAT('2020-01-01T', 
        RIGHT(CONCAT('0', date_column / 10000),2)
        ':'
        RIGHT(CONCAT('0', date_column % 10000 / 100), 2)
        ':'
        RIGHT(CONCAT('0', date_column % 100), 2)));

Example db<>fiddle

CodePudding user response：

Instead of worrying about creating a string to be converted - convert the time to seconds and just add that to your date:

Declare @myDate int = 20220711
      , @myTime int = 182233;

 Select cast(@myDate As char(10))
      , @myTime / 10000 * 60 * 60
      , @myTime % 10000 / 100 * 60
      , @myTime % 100
      , dateadd(second, (@myTime / 10000 * 60 * 60)   (@myTime % 10000 / 100 * 60)   (@myTime % 100), cast(@myDate As char(10)))