Date time diff

  • boehnc

    SSC Eights!

    Points: 809

    Hello all,

    trying to return the time in minutes between the Date1 and Time1 columns and the Date2 and Time2 columns. Help is much appreciated.

    create table #TimeDiff

    (Date1 date

    ,Time1 varchar(50)

    ,Date2 date

    ,Time2 varchar(50)

    )

    Insert into #TimeDiff

    values

    ('2020-08-05', '0500', '2020-08-05', '0600')

    ,('2020-05-26', '2300', '2020-05-27', '0100')

    ,('2020-05-28', '1300', '2020-05-29', '1610')

    trying to return 60, 120, 190 for minutes

  • Cebisa

    SSC Enthusiast

    Points: 109

    Sounds like you need to create a datetime column for date1 + time1 , then date2 + time2

    After this do a a simple datediff for minute

  • Steve Collins

    Ten Centuries

    Points: 1114

    [Edit] I thought there was a typo in line 2 but the date difference seems to also need to include the day date.  Therefore, there's a typo in line 3 where the date should be '2020-05-28' for both Date1 as well as Date2.  So to add the date and time together the code casts both to datetime and then takes the date difference in minutes.  Something like this

    drop table if exists #TimeDiff;
    go
    create table #TimeDiff(
    Date1 date
    ,Time1 varchar(50)
    ,Date2 date
    ,Time2 varchar(50));

    Insert into #TimeDiff values
    ('2020-08-05', '0500', '2020-08-05', '0600')
    ,('2020-05-26', '2300', '2020-05-27', '0100')
    ,('2020-05-28', '1300', '2020-05-28', '1610');

    select datediff(minute,
    cast(Date1 as datetime)+convert(datetime,stuff(Time1,3,0,':')),
    cast(Date2 as datetime)+convert(datetime,stuff(Time2,3,0,':'))) date_diff
    from #TimeDiff;

    Output (now it matches)

    date_diff
    60
    120
    190

    • This reply was modified 1 week ago by  Steve Collins.
    • This reply was modified 1 week ago by  Steve Collins. Reason: Combined dates and times by converting to datetime

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Michael L John

    One Orange Chip

    Points: 25965

    boehnc wrote:

    Hello all,

    trying to return the time in minutes between the Date1 and Time1 columns and the Date2 and Time2 columns. Help is much appreciated.

    create table #TimeDiff

    (Date1 date

    ,Time1 varchar(50)

    ,Date2 date

    ,Time2 varchar(50)

    )

    Insert into #TimeDiff

    values

    ('2020-08-05', '0500', '2020-08-05', '0600')

    ,('2020-05-26', '2300', '2020-05-27', '0100')

    ,('2020-05-28', '1300', '2020-05-29', '1610')

    trying to return 60, 120, 190 for minutes

    Like Cebisa said, why are you not using a datatime2, datetime, or data and time datatypes?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply