CODEWITHSUNDEEP
sqlsql-servert-sql
David Britz
David Britz

Reputation: 127

Difficult SQL Server Query - Converting 12-to-24 hour time

I have a few large database tables with time fields that are in 12-hour AM/PM format. They're varchar(10) fields (nothing I can do about that). Anyway, my task is to change them to 24-hour format. Here's the "original" data with a couple columns I added. Last two columns are "helper" columns I made from string functions.

enter image description here

So my logic here is that if I can separate the hours and if the last two are "PM" then add 12 hours, or something like that. My main question is whether the syntax I'm using is correct. When I run it, I get a single row, so I'm not sure if I'm converting the data correct. Basically I want to take that top table, parse out the hours from CaptureTime1 into an integer, add 12 if needed, and (eventually) get another column with the "new" hours and then paste the time back together.

I'm probably making this WAY too complicated, so I could use feedback on the code below, but if anyone has a more efficient way for me to do this (I'm 90% sure that's true), I'm all ears.

enter image description here

Upvotes: 0

Views: 185

Answers (2)

Isaac
Isaac

Reputation: 3363

If I understand your objective it seems to me you are overthinking this. Does this work for you?

SELECT LEFT(CONVERT(TIME, CaptureTime, 8), 5) AS NewHours
FROM Capture

Upvotes: 1

donPablo
donPablo

Reputation: 1959

Just use SQL

SELECT CaptureDate
      ,CaptureTime
      ,left(convert(varchar, CONVERT(DATETIME, CaptureTime, 102), 24),5) as t24
  FROM [StackOver].[dbo].[Convert12to24hourtime]

CaptureDate             CaptureTime t24
2019-05-20 12:00:00.000 07:55       07:55
2019-06-22 00:00:00.000 08:43 AM    08:43
2019-06-22 12:00:00.000 08:51 am    08:51
2019-06-28 00:00:00.000 12:07 PM    12:07

Upvotes: 1

Related Questions