I have this type of data
TimeOFDay column is varchar. I want to change this time in 24 hour time, using SQL, and updating TwentyFourHourTime column. TwentyFourHourTime column is also varchar.
How can I do this.
Thanks.
I have this type of data
TimeOFDay column is varchar. I want to change this time in 24 hour time, using SQL, and updating TwentyFourHourTime column. TwentyFourHourTime column is also varchar.
How can I do this.
Thanks.
try the following query:
update tableName set TwentyFourHourTime =
case TimeMeridiem
WHEN 'PM' THEN Convert(varchar,dateadd(hour,12,TimeOFDay),108)
ELSE Convert(varchar,TimeOFDay,108)
end
i've written this test - seems to work. works with midnight and adding a leading zero.
UPDATED:
DROP TABLE #times
CREATE TABLE #times
(
TimeOfDay VARCHAR(32),
TimeMeridiem VARCHAR(32),
TwentyFourHourTime VARCHAR(32)
)
INSERT INTO #times (TimeOfDay, TimeMeridiem) VALUES ('1:00', 'PM')
INSERT INTO #times (TimeOfDay, TimeMeridiem) VALUES ('1:00', 'AM')
INSERT INTO #times (TimeOfDay, TimeMeridiem) VALUES ('12:00', 'PM')
UPDATE #times SET TwentyFourHourTime = REPLACE(Right(Replicate('0',5) +
REPLACE((SELECT CASE WHEN TimeMeridiem = 'PM' THEN
CAST((SELECT REPLACE(TimeOfDay,':','.')) AS dec(10,2)) + 12 ELSE
(SELECT REPLACE(TimeOfDay,':','.')) END), '24.', '00.') ,5), '.', ':')
SELECT * FROM #times
RESULT:
TimeOfDay TimeMeridiem TwentyFourHourTime
--------- ------------ ------------------
1:00 PM 13:00
1:00 AM 01:00
12:00 PM 00:00