CODEWITHSUNDEEP
sql-serverdatabase
Tamil
Tamil

Reputation: 1203

Convert text column value (12 hours) to 24 hours from varchar column in SQL Server

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

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.

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.

Can anyone please help me?

Thanks in advance.

Upvotes: 0

Views: 174

Answers (1)

Trung Duong
Trung Duong

Reputation: 3475

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 

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

Then convert it to TIME data type 

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(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

 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)

Upvotes: 2

Related Questions