SQL Server Convert 24hour time formatted as string to 12hour time in SQL

wh6knrhe 于 2023-03-22 发布在其他

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

I have a column in my table that is supposed to be in 24hour format but it's a string. A screenshot is shown below. Please how do I convert it to 12hour format in SQL.

Note; The 24hour format appears like integer

sql-server

来源：https://stackoverflow.com/questions/75786895/convert-24hour-time-formatted-as-string-to-12hour-time-in-sql

1条答案

按热度按时间

8ehkhllq1#

try this.

DROP TABLE IF EXISTS #Crime_Data;
CREATE TABLE #Crime_Data
(
    Time_Occ VARCHAR(4)
);

INSERT INTO #Crime_Data VALUES
(''),('8'),('18'),(NULL),('2130'),('345'),('1300'),('1115'),('1205'),
('1544'),('1700'),('450'),('2315'),('2030'),('2200')

SELECT  Time_Occ,
        CASE WHEN LEN(Time_Occ) <= 2
                THEN CONVERT(VARCHAR(2), 12)
            WHEN SUBSTRING(Time_Occ, 1, LEN(Time_Occ)-2) > 12
                THEN CONVERT(VARCHAR(2), SUBSTRING(Time_Occ, 1, LEN(Time_Occ)-2) - 12)
            ELSE CONVERT(VARCHAR(2), SUBSTRING(Time_Occ, 1, LEN(Time_Occ)-2))
        END +
        ':' +
        RIGHT('00' + Time_Occ, 2) +
        CASE WHEN LEN(Time_Occ) <= 2 OR SUBSTRING(Time_Occ, 1, LEN(Time_Occ)-2) < 12
                THEN ' AM'
            ELSE ' PM'
        END AS '12hour Format'
FROM #Crime_Data

赞(0）回复(0）举报 2023-03-22

我来回答

SQL Server Convert 24hour time formatted as string to 12hour time in SQL

1条答案

相关问题

热门标签

最新问答