KAT KAT - 2 months ago 9
SQL Question

Update Char(5) table HH:MM to add 12 for military time

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?

Answer

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