Remove a leading zero from 24 hour time in sql server 2008

Question

I have this code

SELECT convert(varchar(5), getdate(), 108)

which returns 09:00, or whatever the time is.

I want it to return 9:00, and when it's 1 p.m. I want it to return 13:00.

Answer 1

An inelegant but functional way would simply be to test for the presence of a leading zero using a CASE statement, then remove it if it is there:

DECLARE @TheDate DATETIME

SET @TheDate = '2015-04-14 09:30:00.000'
SELECT 
    CASE 
        WHEN LEFT(convert(varchar(5), @TheDate, 108),1) != '0' 
        THEN convert(varchar(5), @TheDate, 108)
        ELSE RIGHT(convert(varchar(5), @TheDate, 108), 4)
      END

SET @TheDate = '2015-04-14 13:00:00.000'
SELECT 
    CASE 
        WHEN LEFT(convert(varchar(5), @TheDate, 108),1) != '0' 
        THEN convert(varchar(5), @TheDate, 108)
        ELSE RIGHT(convert(varchar(5), @TheDate, 108), 4)
      END

SET @TheDate = GETDATE()
SELECT 
    CASE 
        WHEN LEFT(convert(varchar(5), @TheDate, 108),1) != '0' 
        THEN convert(varchar(5), @TheDate, 108)
        ELSE RIGHT(convert(varchar(5), @TheDate, 108), 4)
      END

Slightly more interesting, but without any pretense of simplicity (adapted from this answer):

DECLARE @TheDate DATETIME

SET @TheDate = '2015-04-14 09:30:00.000'
SELECT substring(convert(varchar(5), @TheDate, 108), patindex('%[^0]%',convert(varchar(5), @TheDate, 108)), 10) 

SET @TheDate = '2015-04-14 13:00:00.000'
SELECT substring(convert(varchar(5), @TheDate, 108), patindex('%[^0]%',convert(varchar(5), @TheDate, 108)), 10)

SET @TheDate = GETDATE()
SELECT substring(convert(varchar(5), @TheDate, 108), patindex('%[^0]%',convert(varchar(5), @TheDate, 108)), 10)