CODEWITHSUNDEEP
sqlsql-server-2005
Duke0ne
Duke0ne

Reputation: 15

VARCHAR TIME TO GET THE DIFFERENCE

I already spent a day looking for the right SQL script just to get the difference of the 2 varchar times.

Here are the varchar columns with each value:

So it will gonna be like this: ProcSchedTime - PtEntersRoomTime = 221 (mins ) I want the result would be 221 in minutes, not 389.

SQL 2005 here.

Im sorry for the confusion.

Please and thank you!

Upvotes: 0

Views: 42

Answers (3)

Zorkolot
Zorkolot

Reputation: 2017

This would grab the fields from a table,

SELECT DATEDIFF(MINUTE, LEFT(PtEntersRoomTime, 2) + ':' + RIGHT(PtEntersRoomTime, 2)
                      , LEFT(ProcSchedTime, 2) + ':' + RIGHT(ProcSchedTime,2)
               ) AS [TimeDifference]
  FROM TableName

Upvotes: 0

Sam
Sam

Reputation: 1358

You could make it look like a date time, cast it to a datetime, then use DateDiff 

SELECT DATEDIFF(MINUTE,cast(STUFF('1319', 3, 0, ':') as datetime) , cast(STUFF('0930', 3, 0, ':') as datetime))

The STUFF function is just adding a colon in the middle.

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

To get the difference in mins:

(13 - 09) * 60 + (19 - 30)

select (left(varchartime1,
             2
            ) - left(varchartime2,
                     2
                    )
       ) * 60 + (right(varchartime1,
                       2 
                      ) - right(varchartime2,
                                2
                               )
                )

Hopefully it won't throw type errors

Upvotes: 0

Related Questions