Clever Geek Handbook

Best option for getting time in hh: mm format from two given times

select cast(datediff(minute,convert(time,'09:35 AM'),
       convert(time,'07:06 PM'))/60 as varchar)
       +':'+cast(datediff(minute,convert(time,'09:35 AM'),
       convert(time,'07:06 PM'))%60 as varchar)

Output :9:31

Do we have any other function with which I can shorten the above script. Also, what should I do to get the output 09:31

instead 9:31

?

+3


source to share


4 answers


You can use CONVERT()

with style 114 to getHH:mm 

SELECT 
    CONVERT(nvarchar(5), 
        -- Get difference of time
        CONVERT(datetime,'07:06 PM') - CONVERT(datetime,'09:35 AM')
        , 114)

SQL Fiddle



EDIT

As a comment to avoid datetime arithmetic

SELECT CONVERT(varchar(5), 
        DATEADD(minute, 
            DATEDIFF(minute, convert(time,'09:35 AM'), convert(time,'07:06 PM')) 
        , 0)
    , 114)
+5


source


You can use Format on Sql Server.

Update - (example) : this might be what you want:



select FORMAT(CONVERT(datetime,'9:31'),'HH:mm') AS 'time'
0


source 


DECLARE @time1 AS VARCHAR(8) = '09:35 AM'
DECLARE @time2 AS VARCHAR(8) = '07:06 PM'

SELECT 
    time1   = CAST(@time1 AS TIME), 
    time2   = CAST(@time2 AS TIME),
    diff    = DATEDIFF(MINUTE, @time1, @time2),
    hh      = RIGHT('00' + CAST(DATEDIFF(MINUTE, CAST(@time1 AS TIME), CAST(@time2 AS TIME))/60 AS VARCHAR(2)), 2),
    mm      = RIGHT('00' + CAST(DATEDIFF(MINUTE, CAST(@time1 AS TIME), CAST(@time2 AS TIME))%60 AS VARCHAR(2)), 2),
    final   = RIGHT('00' + CAST(DATEDIFF(MINUTE, CAST(@time1 AS TIME), CAST(@time2 AS TIME))/60 AS VARCHAR(2)), 2) + ':' +
              RIGHT('00' + CAST(DATEDIFF(MINUTE, CAST(@time1 AS TIME), CAST(@time2 AS TIME))%60 AS VARCHAR(2)), 2)

When declaring and / or converting to a type, VARCHAR

you must specify the length, otherwise SQL-Server will assign you a default value. Sometimes this 1

, sometimes this 30

. See the article for more information .

Also, to format the difference in hh:mm

, you need to fill your lines with the appropriate number of zero. This is done with:

RIGHT('00' + str, 2)
0


source


Just try to check this ad if you need it:

SELECT FORMAT(GETDATE(),'hh:mm')

OR

SELECT (CONVERT(VARCHAR(5), GETDATE(), 114))
0


source






More articles:


All Articles