We have a table that is char(5) and has data inserted like 6:45, 10:15, 3:00. Our business hours are from 6-3pm...and this table doesn't have am/pm included -a as it is char(5).... I cannot change the table, I need to change the data daily after-the-upload. All of the data loaded is x:xx or xx:xx format.
Yes, they 'should' convert before loading table, but they aren't. How can I go in and 'fix' the entries... changing the 6:00,7:00,8:00,9:00,to have a 06,07,08,09 (leading zero) and change the 1, 2, 3, 4, 5, beginning number to 13,14,15,16 with keeping the rest of the :xx part of the data?
Assuming you never have data between 1AM and 5AM, and can safely assume those values need 12 hours added... then you can convert to a TIME type, evaluate the HOUR value, and add 12 only when its between 1 and 5 - and convert it all back to a char(5) to update.
DECLARE @Sample TABLE (badTime CHAR(5)) INSERT INTO @Sample(badTime) VALUES ('6:45'), ('07:45'), ('8:45'), ('9:45'), ('10:45'), ('11:45'), ('12:45'), ('1:45'), ('2:45'), ('3:45'), (null), ('') SELECT *, CONVERT(VARCHAR(5),CASE WHEN DATEPART(HH,CONVERT(TIME,badTime)) BETWEEN 1 AND 5 THEN DATEADD(HH,12,CONVERT(TIME,badTime)) ELSE CONVERT(TIME,badTime) END,108) -- UPDATE S SET badTime = CONVERT(VARCHAR(5),CASE WHEN DATEPART(HH,CONVERT(TIME,badTime)) BETWEEN 1 AND 5 THEN DATEADD(HH,12,CONVERT(TIME,badTime)) ELSE CONVERT(TIME,badTime) END,108) FROM @Sample S WHERE badTime IS NOT NULL AND badTime!='' AND ISDATE(badTime)=1