-3

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Pallavi Singh
  • 55
  • 5
  • 1
    use [convert()](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15) – Squirrel Jan 17 '22 at 02:51

2 Answers2

6

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:

SaneOutput BadOutput
2021-10-13 21:38:20.000 2021/10/13 21:38:20

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
2

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:

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

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • 3
    Don’t forget this reference, which shows how bad `format` is at scale: https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but – Aaron Bertrand Jan 17 '22 at 04:06
  • @AaronBertrand, understood. It is very expensive. Thanks for the enlightening post. I have updated my answer. – Venkataraman R Jan 17 '22 at 04:12