# 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: 112

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: 1126

 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;gocreate 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_difffrom #TimeDiff;`

Output (now it matches)

`date_diff60120190`

• This reply was modified 1 week, 4 days ago by  Steve Collins.
• This reply was modified 1 week, 4 days 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: 25975

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)