Datediff to Output Hours and Minutes

DateDiff to output hours and minutes

Small change like this can be done

  SELECT  EmplID
        , EmplName
        , InTime
        , [TimeOut]
        , [DateVisited]
        , CASE WHEN minpart=0 
        THEN CAST(hourpart as nvarchar(200))+':00' 
        ELSE CAST((hourpart-1) as nvarchar(200))+':'+ CAST(minpart as nvarchar(200))END as 'total time'
        FROM 
        (
        SELECT   EmplID, EmplName, InTime, [TimeOut], [DateVisited],
        DATEDIFF(Hour,InTime, [TimeOut]) as hourpart, 
        DATEDIFF(minute,InTime, [TimeOut])%60 as minpart  
        from times) source

DATEDIFF - Display result in Hours and minutes

You can calculate the hours by division as you stated. You can then use modulo to calculate the minutes.

declare @MondayOpenTime time = '09:00'
    , @MondayCloseTime time = '17:30'

select convert(varchar(2), DateDiff(Minute, @MondayOpenTime , @MondayCloseTime) / 60) + ':' + right('0' + convert(varchar(2), DateDiff(Minute, @MondayOpenTime , @MondayCloseTime) % 60), 2)

DateDiff() Hours and Minutes?

I went with

DATEDIFF(second, start_date, end_date) / 3600.0          

Thank you all for the answers...!!.

Calculate date difference in (hours,mins) in SQL Server

Don't store the difference as hours and minutes. Just use decimal hours:

select datediff(second, submitted, approved) / (60.0 * 60) as decimal_hours

The extension is much trickier:

select (case when cast(submitted as date) = cast(approved as date)
             then datediff(second, submitted, approved)
             else (datediff(day, submitted, approved) - 1) * 10*60*60 +
                  datediff(second, cast(submitted as time), '20:00') +
                  datediff(second, '08:00', cast(approved as time))
        end) / (60.0 * 60) as decimal_hours

If you have more extensions -- such as avoiding weekends and holidays -- then ask another question. Don't edit this one.

SQL - datediff returns hours, but I want to add minutes and seconds in HH:MM:SS

You can convert the number of seconds as a time. I don't easily follow the logic of your code, but the conversion is simple enough:

select cast(dateadd(second,
                    datediff(second, <starttime>, <enddatime>),
                    0) as time
           )

How to use datediff function to show day, hour, minute, and second

select 
datediff(second,depart_dt, arrived_dt)/86400 as Day,
datediff(second,depart_dt, arrived_dt)/3600%24 as Hour,
datediff(second,depart_dt, arrived_dt)/60%60 as Minute,
datediff(second,depart_dt, arrived_dt)%60 as Second
from yourtable

Converting days, hours and minutes into seconds for accuracy.

SQL Fiddle: http://sqlfiddle.com/#!6/c6c63/1

I want Hours,Min, second difference from two datetime

this code might help you...

DECLARE @First datetime
DECLARE @Second datetime
SET @First = '04/02/2008 05:23:22'
SET @Second = getdate()

SELECT DATEDIFF(day,@First,@Second)*24 as TotalHours,
DATEDIFF(day,@First,@Second)*24*60 as TotalMinutes,
DATEDIFF(day,@First,@Second)*24*60*60 as TotalSeconds

Date difference formatted in Days Hours Minutes Seconds

The following select should work:

DECLARE @x int, 
        @dt1 smalldatetime = '1996-03-25 03:24:16', 
        @dt2 smalldatetime = getdate()

SET @x = datediff (s, @dt1, @dt2)

SELECT convert(varchar, @x / (60 * 60 * 24)) + ':'
+ convert(varchar, dateadd(s, @x, convert(datetime2, '0001-01-01')), 108)

Another option that is closer to your desired output is:

DECLARE @start DATETIME ,
    @end DATETIME,
    @x INT

SELECT  @start = '2009-01-01' ,
        @end = DATEADD(ss, 5, DATEADD(mi, 52, DATEADD(hh, 18, DATEADD(dd, 2, @start)))),
        @x = DATEDIFF(s, @start, @end)

SELECT  CONVERT(VARCHAR, DATEDIFF(dd, @start, @end)) + ' Days '
        + CONVERT(VARCHAR, DATEDIFF(hh, @start, @end) % 24) + ' Hours '
        + CONVERT(VARCHAR, DATEDIFF(mi, @start, @end) % 60) + ' Minutes '
        + CONVERT(VARCHAR, DATEPART(ss, DATEADD(s, @x, CONVERT(DATETIME2, '0001-01-01')))) + ' Seconds'

UPDATED ANSWER (04/12/2018): Accounts for difference in lower order date part that affects the higher order date part (i.e. 23 hour difference will now result in 0 days 23 hours)!

DECLARE @start DATETIME = '2018-04-12 15:53:33' ,
@end DATETIME = '2018-04-13 14:54:32' ,
@x INT;

SET @x = DATEDIFF(s, @start, @end);

SELECT  CONVERT(VARCHAR(10), ( @x / 86400 )) + ' Days '
        + CONVERT(VARCHAR(10), ( ( @x % 86400 ) / 3600 )) + ' Hours '
        + CONVERT(VARCHAR(10), ( ( ( @x % 86400 ) % 3600 ) / 60 ))
        + ' Minutes ' + CONVERT(VARCHAR(10), ( ( ( @x % 86400 ) % 3600 ) % 60 ))
        + ' Seconds';

---

Related Topics