Query to Convert a specific 12 hour format type to 24 hour format type in sql server 2016

Question

We have a case where the time edited by users is in the format 3/2/20 3:30P (where P denotes P.m.) which I'm trying to convert to the 24hour format to be able to use a join condition with a standard column to something like 2020-03-02 15:30:0000

e.g. 3/2/20 3:30P to 2020-03-02 15:30:0000

Tried the below ways in one of the answers in StackOverflow, but it didn't work:

SELECT CONVERT(DATETIME, '10/1/2013 6:39:04  PM', 0)  
-- Concatenate in required format
SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, '10/1/2013 6:39:04  PM', 0), 101) 
+ ' '+ CONVERT(VARCHAR(5),CONVERT(DATETIME, '10/1/2013 6:39:04  PM', 0), 108) 

Answer 1

Assuming that the format is M/d/yy h:mmA (or P) then I would use the style code 22, which is for mm/dd/yy hh:mi:ss AM (or PM), so that this works regardless of language setting:

SELECT CONVERT(datetime2(0),V.YourDate + 'M',22)
FROM (VALUES('3/2/20 3:30P'))V(YourDate);

Answer 2

You can do :

DECLARE @dates VARCHAR(255) = '3/2/20 3:30P'

SELECT @dates AS Old_Date, 
       CONVERT(DATETIME, @dates + RIGHT(RIGHT(@dates, 1) + 'm', 1)) AS New_Date