CODEWITHSUNDEEP
sqlsql-server
J.Doe
J.Doe

Reputation: 339

SQL, Concatenate a character and leading 0 depending on the value

I have a table with columns Hour and Minute, both containing an integer. (There are other columns as well). Now, I want to create a view from this table and I need to combine these two columns' data into one column with a ':' symbol. For example, if a record has 8 for Hour, 10 for Minute, the combined column should contain 08:10. One digit numbers need to be followed by a leading 0, which initially does not exist in Hour and Minute columns.

I was successfully able to convert the integer to varchar and concatenate the numbers with ':' with the following SQL command

/* Combined Column name "Time" */
SELECT
  Cast([Hour] as varchar) + ':' + Cast([Minute] as varchar) As Time
FROM Table1

but I am not sure how I can add leading 0 to this only if the number is one digit. Can someone please help me with this? Thank you.

Upvotes: 2

Views: 2063

Answers (3)

M_Idrees
M_Idrees

Reputation: 2172

You can create scalar function to pad characters:

CREATE FUNCTION dbo.PadNumbersOnLeft (@NumberInput INT, @NoOfCharsToPad tinyint)
RETURNS VARCHAR(250)
AS BEGIN
    DECLARE @ResultString VARCHAR(250)

    SET @ResultString = LTRIM(@NumberInput)

    IF(@NoOfCharsToPad > LEN(@ResultString))
        SET @ResultString = REPLICATE('0', @NoOfCharsToPad - LEN(@ResultString)) + @ResultString;

    RETURN @ResultString;
END;

And here is the example how to use this:

declare @hour int = 8
declare @minute int = 35

select 
[dbo].[PadNumbersOnLeft] (@hour, 2) + ':' + [dbo].[PadNumbersOnLeft] (@minute, 2)

You can replace your columns names with @hour and @minute variables.

Upvotes: 0

Rohit Padma
Rohit Padma

Reputation: 603

One Other Approach :

SELECT
 (CASE WHEN LEN(Cast([Hour] as varchar))=1 THEN '0'+Cast([Hour] as varchar) ELSE Cast([Hour] as varchar)  END) + ':' +
   (CASE WHEN LEN(Cast([Minute] as varchar))=1 THEN '0'+Cast([Minute] as varchar) ELSE Cast([Minute] as varchar)  END)  As Time
FROM Table1

Hope its helps you ! :)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Use timefromparts() (SQL Server 2012+) to create a proper time value:

select timefromparts(hour, minute, 0, 0, 0)

You can then format this as a string, if that is what you really want to do:

select convert(varchar(5), timefromparts(hour, minute, 0, 0, 0))

Upvotes: 0

Related Questions