I have a datetime (your standard yyyy-mm-dd hh:mm:ss) column and a varchar column that holds an appt time that looks like this: 1030 or 0735. I need to be able to calculate a difference to determine a wait time.

CELKO (9/26/2012)

---

Wish we had DDL, but the first nameless column is DATETIME2(0) and the second one TIME(0) or another DATETIME2(0) with the complete timestamp in it. If you use TIME(0), then you can use "CAST (nameless AS DATE) + appointment_time" to get the second argument for a DATEDIFF() call.

dwain.c (9/26/2012)

---

Nice set up data Sean!

Using it, I'd like to offer a slightly less verbose solution:

SELECT *, WaitTime=DATEDIFF(minute
    ,CAST(check_in AS TIME)
    ,CAST(STUFF(appt_time, 3, 0, ':') AS TIME))
FROM #CheckIn

The STUFF apparently forces the datatype to VARCHAR so minutes aren't lost.

Both solutions return negative minutes, so you may want to take that into account.

dwain.c (9/26/2012)

---

Nice set up data Sean!

Using it, I'd like to offer a slightly less verbose solution:

SELECT *, WaitTime=DATEDIFF(minute
    ,CAST(check_in AS TIME)
    ,CAST(STUFF(appt_time, 3, 0, ':') AS TIME))
FROM #CheckIn

The STUFF apparently forces the datatype to VARCHAR so minutes aren't lost.

Both solutions return negative minutes, so you may want to take that into account.

ScottPletcher (10/2/2012)

---

I think it needs one (semi)slight adjustment:

SELECT *, WaitTime=DATEDIFF(minute
,CAST(check_in AS TIME)
,CAST(STUFF(RIGHT('000' + CAST(appt_time AS varchar(4)), 4), 3, 0, ':') AS TIME))
FROM #CheckIn

Sean Lange (9/27/2012)

---

That's pretty cool Dwain. Didn't think of using stuff, I went the hard away around.