Converting string to datetime problem

Question

Using SQL Server 2000:

SELECT PERSONID, 
       CARDEVENTDATE, 
       INTIME, 
       CASE 
         WHEN OUTTIME = INTIME THEN 
           'No PunchOut' 
         ELSE 
            OUTTIME 
       END AS OUTTIME, 
       CONVERT(char(8), CASE 
                          WHEN DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) > '18:00:00' THEN 
                            Cast('18:00:00' AS datetime) 
                          ELSE 
                            DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) 
                        END - CASE 
                                WHEN DateAdd(Day, - DateDiff(Day, 0, InTime), InTime) < '09:00:00' THEN 
                                  Cast('09:00:00' AS datetime) 
                                ELSE 
                                  DateAdd(Day, - DateDiff(Day, 0, InTime), InTime) 
                              END, 8) AS WorkTime
  FROM (SELECT T_PERSON.PERSONID,
               T_CARDEVENT.CARDEVENTDATE, 
               MIN(T_CARDEVENT.CARDEVENTTIME) AS INTIME, 
               MAX(T_CARDEVENT.CARDEVENTTIME) AS OUTTIME
          FROM T_PERSON 
    INNER JOIN T_CARDEVENT ON T_PERSON.PERSONID = T_CARDEVENT.PERSONID 
      GROUP BY T_PERSON.PERSONID, T_CARDEVENT.CARDEVENTDATE) DERIVEDTBL

---

T_cardevent.cardeventtime column datatype is Varchar.

In table Cardeventtime values are 080002, 091235.... so on...,

When I executing the above query it showing Arithmetic Express Overflow error for converting expression to datatype Datetime.

Answer 1

So this "080002" stands for? 8 hours, 0 minutes, 2 seconds?

This is definitely not a valid DATETIME format out of the box - and it doesn't comply with any of the valid SQL Server CONVERT styles, either.

So you'll have to do some conversions yourself, manually. Is there any chance you could wrap the table with this column into a view which could handle the conversion?

You'd have to do something along the lines of:

  CONVERT(DATETIME, SUBSTRING(CardEventTime, 1, 2) + ':' + 
                    SUBSTRING(CardEventTime, 3, 2) + ':' +
                    SUBSTRING(CardEventTime, 5, 2), 8)

and this should turn your "080002" into "08:00:02" which can then be converted to a DATETIME (no separate time datatype until SQL Server 2008) using the style no. 8 (hh:mm:ss).

Marc

Answer 2

I've made a series of assumptions here, and worked without being able to test it, but here's a possible solution:

SELECT PERSONID, 
       CARDEVENTDATE, 
       INTIME, 
       CASE 
         WHEN OUTTIME = INTIME THEN 
           'No PunchOut' 
         ELSE 
            OUTTIME 
       END AS OUTTIME, 
    Stuff(Stuff(Right('000000' +
     CONVERT(varchar(5), CASE 
                          WHEN Convert(int,OutTime) > 180000 THEN 
                            180000
                          ELSE 
                            Convert(int,OutTime)
                        END - CASE 
                                WHEN Convert(int,InTime) < 90000 THEN 
                                  90000 
                                ELSE 
                                  Convert(int,InTime) 
                              END), 6),5,0,':'),3,0,':')
     AS WorkTime
  FROM (SELECT T_PERSON.PERSONID,
               T_CARDEVENT.CARDEVENTDATE, 
               MIN(T_CARDEVENT.CARDEVENTTIME) AS INTIME, 
               MAX(T_CARDEVENT.CARDEVENTTIME) AS OUTTIME
          FROM T_PERSON 
    INNER JOIN T_CARDEVENT ON T_PERSON.PERSONID = T_CARDEVENT.PERSONID 
      GROUP BY T_PERSON.PERSONID, T_CARDEVENT.CARDEVENTDATE) DERIVEDTBL

I've not used Stuff very frequently, so the insertion points may be off.