SQL Server Convert AM/PM datetime to 24 hour when both formats present in a column

jogvjijk 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(113)

I have a messy table with a column called date_time . It has values in both AM/PM and 24 hour format (see example below). I am creating a new table using the messy table with the following code:
| ID | date_time |
| ------------ | ------------ |
| 1 | 1/24/2022 7:08:00 PM |
| 2 | 1/24/2022 17:37 |
| 3 | 1/24/2022 9:36:00 PM |
| 4 | 1/24/2022 22:14 |

CREATE TABLE NEW_TABLE (ID INT, date_time datetime)

SELECT
  ID, date_time
INTO NEW_TABLE
FROM MESSY_TABLE

Desired conversion:

ID	date_time
1	1/24/2022 19:08
2	1/24/2022 17:37
3	1/24/2022 21:36
4	1/24/2022 22:14

sql-server

来源：https://stackoverflow.com/questions/76440979/convert-am-pm-datetime-to-24-hour-when-both-formats-present-in-a-column

1条答案

按热度按时间

ugmeyewa1#

Just use convert with appropriate style for date portion of the input string. SQL Server seems to handle 12 and 24 hours format time as expected:

select id, date_time, convert(datetime, date_time, 101) as date_time_value
from (values
    (1, '1/24/2022 7:08:00 PM'),
    (2, '1/24/2022 17:37'),
    (3, '1/24/2022 9:36:00 PM'),
    (4, '1/24/2022 22:14'),
    (5, '1/24/2022 05:37')
) as t(id, date_time)

-- id  date_time               date_time_value
-- 1   1/24/2022 7:08:00 PM    2022-01-24 19:08:00.000
-- 2   1/24/2022 17:37         2022-01-24 17:37:00.000
-- 3   1/24/2022 9:36:00 PM    2022-01-24 21:36:00.000
-- 4   1/24/2022 22:14         2022-01-24 22:14:00.000
-- 5   1/24/2022 05:37         2022-01-24 05:37:00.000

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Convert AM/PM datetime to 24 hour when both formats present in a column

1条答案

相关问题

热门标签

最新问答