1

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.

J.Doe
  • 329
  • 3
  • 14
  • 2
    Possible duplicate of [Pad a string with leading zeros so it's 3 characters long in SQL Server 2008](https://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) – Siyual Aug 14 '17 at 13:49
  • With example in question, to format time you should go with Gordon's answer, but for general trick how to add padding zeros check link in previous comment. – Nenad Zivkovic Aug 14 '17 at 14:49

3 Answers3

1

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 ! :)

Rohit Padma
  • 603
  • 5
  • 15
  • 1
    Thank you everyone, but I decided to go with this one. This was close to what I already had! Thanks! – J.Doe Aug 14 '17 at 14:52
0

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))
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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.

M_Idrees
  • 2,080
  • 2
  • 23
  • 53