Date time diff

Question

Post reply

Date time diff

boehnc

Ten Centuries

Points: 1003
More actions
October 17, 2020 at 6:05 pm
Go to Answer
#3798389

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
Steve Collins

SSCrazy

Points: 2152
More actions
October 18, 2020 at 6:49 pm
Answer
#3798608
[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 4 years ago by Steve Collins.
- This reply was modified 4 years 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

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

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

Cebisa SSC Enthusiast Points: 151 More actions · Answer 1

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

After this do a a simple datediff for minute

Michael L John One Orange Chip Points: 27054 More actions · Answer 2

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/