CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008datetime
peter
peter

Reputation: 2516

How to get Time difference Between Datetime, Date and Time columns

I have table called Data_Details and the data looks like:

DateTimeClosed            Datesub            TimeSub
6/20/2011 18:00           5/16/2011          17:13:17
6/20/2011 18:00           5/18/2011          13:45:17
6/1/2011 19:00            5/24/2011          8:30:12

I am trying to get the difference between closed date and sub date in mins.

I wrote something like this:

SELECT  convert(int,convert(Datetime,[DateTimeClosed])-
(convert(Datetime,[Datesub])+convert(datetime,[TimeSub])))*24*60 
FROM dbo.Data_details

It is giving me following error:

Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string.

Can anyone help me?

Upvotes: 0

Views: 3217

Answers (4)

Icarus
Icarus

Reputation: 63956

This returns time difference in seconds:

select datediff(SECOND,'5/16/2011'+' '+'17:13:17','6/20/2011 18:00')

Sample script:

declare @test as table
(
date1 datetime,
date2 date,
date3 time
)
insert into @test 
values
('6/20/2011 18:00'         ,  '5/16/2011'        ,  '17:13:17')
select Datediff(second,cast (date2 as varchar)+' '+ cast(date3 as varchar),date1) from @test

UPDATE- Using varchars now that OP clarified that they are all varchars:

declare @test as table
(
date1 varchar(50),
date2 varchar(50),
date3 varchar(50)
)
insert into @test 
values
('6/20/2011 18:00'         ,  '5/16/2011'        ,  '17:13:17')
select Datediff(second,cast (date2 +' '+date3 as datetime),cast (date1 as datetime)) from @test

Upvotes: 2

Dibstar
Dibstar

Reputation: 2364

SET DATEFORMAT MDY
;with dates AS 
(
SELECT '6/20/2011 18:00' as datetimeclosed,'5/16/2011' as datesub,'17:13:17' as timesub UNION ALL
SELECT '6/20/2011 18:00','5/18/2011','13:45:17' UNION ALL
SELECT '6/1/2011 19:00','5/24/2011','8:30:12' 
)
SELECT
DATEDIFF(minute,CAST(datesub + ' ' + timesub AS DATETIME),CAST(datetimeclosed AS DATETIME)) as time_diff_in_minutes
 FROM dates

Upvotes: 0

Nick O
Nick O

Reputation: 3826

I think you looking for DateDiff

This example will return minutes:

SELECT DATEDIFF(m, CONVERT(datetime, [Datesub] + ' ' + [TimeSub]), CONVERT(datetime, [DateTimeClosed]))
FROM dbo.Data_details

Upvotes: 0

Lamak
Lamak

Reputation: 70638

Try this:

SELECT DATEDIFF(MINUTE,CONVERT(DATETIME,Datesub + ' ' + TimeSub,101),CONVERT(DATETIME,DateTimeClosed,101))
FROM yourTable

Upvotes: 0

Related Questions