I need to convert "13/10/2021 09:38:20 PM" to "2021/10/13 21:38:20" in SQL Server 2019. I have looked up the internet for the same but I can't find a solution. Please help me.

CodePudding user response：

Why are you starting with a bad regional format? Why do you care about what format SQL Server provides? Let SQL Server interpret the ambiguous regional format and present a proper datetime data type (or, better yet, fix the source so it isn't providing ambiguous formats in the first place). If you want to present it as something like yyyy/mm/dd 24:nn:ss let the presentation tier do that.

That said, you can do this as follows:

DECLARE @BadDateString varchar(32) = '13/10/2021 09:38:20 PM',
        @SaneOutput    datetime;

SET @SaneOutput = CONVERT(datetime, @BadDateString, 103);

SELECT SaneOutput = @SaneOutput,
       BadOutput  = CONVERT(char(11), @SaneOutput, 111)
                    CONVERT(char(8),  @SaneOutput, 108);

Output:

This is complex because the format you want isn't available natively (well, unless you use the extremely expensive FORMAT() function that I'm sure someone will recommend). Lots more about Dating Responsibly here.

CodePudding user response：

As you have to convert from one format to another format, first you have to convert into datetime datatype and then apply custom format as given below:

DECLARE @datestring VARCHAR(50) = '13/10/2021 09:38:20 PM'
SELECT FORMAT(CONVERT(DATETIME,@datestring,103),'yyyy/MM/dd HH:mm:ss')

2021/10/13 21:38:20

References:

• FORMAT
• Standard Datetime format strings
• Custom Datetime format strings

CAVEAT: Using 'FORMAT` is expensive. So, make a decision accordingly. FORMAT is expensive