2
SELECT   Warehouses.Name,
         RIGHT(CONVERT(VARCHAR,AirwayBillTrucks.CheckOutTime,100),7) AS CheckOutTime,
         RIGHT(CONVERT(VARCHAR,AirwayBillTrucks.ReturnTime,100),7) AS ReturnTime

That is my piece of code, CHECKOUTTIME and RETURNTIME used to be DATETIME DD-DD-YYYY HH-MM-SS but I converted them into TIME HH-MM-SS tt which is fine for me. Now I need to now the difference between those two columns in seconds,

Sabi Tech
  • 81
  • 1
  • 12
  • 1
    Are you saying you use TIME datatype for those columns? if so, how you can work with time 23:30 with 01:30 the next day? – FLICKER Jun 29 '16 at 23:19
  • @FLICKER i made it into 12 hrs. – Sabi Tech Jun 29 '16 at 23:22
  • 1
    @AbleTech. Still I don't get how you know if 09:34 is for today or the next day? unless you have another fields that tells you if they are in the same day. just curious. – FLICKER Jun 29 '16 at 23:25
  • @FLICKER yes I have another field two fields I made another two columns and called them expr1 and expr2 (datetime) but that's just for reference, I don't have the problem of not knowing if this 9:30 if from today or tomorrow because all I want to do is get an average per month. – Sabi Tech Jun 29 '16 at 23:30
  • 1
    Most often, losing the date information and keeping only time information in a field is misguided. It isn't always wrong, but it is very often wrong. Flicker is observing that if you have only time information, you can't formally tell how many days are between the events, so your answer can be off by a multiple of 86,400 seconds (the number of seconds in a day). Using 12-hour clock only makes matters worse. – Jonathan Leffler Jun 29 '16 at 23:30
  • I understand but in this case I just need to know the difference between those two columns. – Sabi Tech Jun 29 '16 at 23:44
  • If `CheckOutTime` and `ReturnTime` might occur on different dates, then keeping them as `time` datatype doesn't make any sense. The difference between `09:00:00` and `09:00:30` is 30 seconds, but if they are one day apart then the **actual** difference is *86,430* seconds. – Zohar Peled Jun 30 '16 at 05:34

1 Answers1

4
SELECT DATEDIFF(ss,CheckOutTime,CheckInTime)
Sam
  • 850
  • 1
  • 10
  • 20