从SQL Server中的varchar列将文本列值（12小时）转换为24小时

In my existing SQL Server database, there is a column like this:

在我现有的SQL Server数据库中,有一个这样的列:

Column name and type: meeting_time varchar(22)

列名和类型:meeting_time varchar(22)

Values stored in different format like 02:30:PM / 2:30:PM / 14:00:AM / 10:00:00:AM / 9:2:PM

以不同格式存储的值,如02:30:PM / 2:30:PM / 14:00:AM / 10:00:00:AM / 9:2:PM

I am doing migration related work. In the existing database this kind of values were stored. I want to convert those into 24 hours format.

我正在做与移民有关的工作。在现有数据库中存储了这种值。我想将它们转换为24小时格式。

When I tried with stuff command with combination of IIF. It keep on increasing and I don't know the latest db has some other values. Since existing application, values can be entered by user and stored it in the database.

当我尝试使用IIF组合的stuff命令时。它继续增加,我不知道最新的db有一些其他值。从现有应用程序开始,用户可以输入值并将其存储在数据库中。

Can anyone please help me?

谁能帮帮我吗?

Thanks in advance.

提前致谢。

1 个解决方案

#1

First you must convert values stored in meeting_time column into valid time format. Example 02:30:PM to 02:30 PM, 10:00:00:AM to 10:00:00 AM. I think you could use

首先,您必须将存储在meeting_time列中的值转换为有效的时间格式。例02:30:PM到02:30 PM,10:00:00:AM到10:00:00 AM。我想你可以用

REPLACE(REPLACE(meeting_time, ':AM', ' AM'), ':PM', ' PM')

Then convert it to TIME data type

然后将其转换为TIME数据类型

CAST(REPLACE(REPLACE(meeting_time, ':AM', ' AM'), ':PM', ' PM') AS TIME)

And then format the converted time as format that you want (the value 108 in below CONVERT function specifies the 24-hour time format)

然后将转换后的时间格式化为您想要的格式(CONVERT函数中的值108指定24小时时间格式)

CONVERT(varchar(22), CAST(REPLACE(REPLACE(meeting_time, ':AM', ' AM'), ':PM', ' PM') AS TIME), 108)

If you are using MS SQL Server 2012 (or newer) and you also want to try converting invalid value, then we could used TRY_CAST instead CAST

如果您正在使用MS SQL Server 2012(或更新版本)并且您还想尝试转换无效值,那么我们可以使用TRY_CAST代替CAST

 CONVERT(varchar(22),
        IIF(TRY_CAST(REPLACE(REPLACE(meeting_time, ':AM', ' AM'), ':PM', ' PM') AS TIME) IS NULL, 
            TRY_CAST(REPLACE(REPLACE(meeting_time, ':AM', ''), ':PM', '') AS TIME),
            CAST(REPLACE(REPLACE(meeting_time, ':AM', ' AM'), ':PM', ' PM') AS TIME)) 
      , 108)

#1