CODEWITHSUNDEEP
sqlsql-serverdatedatetime
MAK
MAK

Reputation: 7260

Convert any datetime format to yyyy-mm-dd hh:mm:ss.sss

I have the following two different formats of dates as shown below:

DECLARE @A VARCHAR(50) = '2019-11-06 00:00:00'
DECLARE @A1 VARCHAR(50) = '06-11-2019 00:00:00'

I need to write a single conversion statement which convert any of above format to yyyy-mm-dd hh:mm:ss.sss.

Tried:

SELECT CONVERT(DATETIME,@A) AS A, CONVERT(DATETIME,@A1) AS A1

Getting:

A                           A1
---------------------------------------------------------
2019-11-06 00:00:00.000     2019-06-11 00:00:00.000

Expected:

A                           A1
---------------------------------------------------------
2019-11-06 00:00:00.000     2019-11-06 00:00:00.000

I don't want to write two different convertion statement because I never know which input date is of which format.

Upvotes: 1

Views: 4681

Answers (3)

Ed Bangga
Ed Bangga

Reputation: 13006

You can check the date formatting. Then apply the desired conversion.

DECLARE @A VARCHAR(50) = '2019-11-06 00:00:00'
DECLARE @A1 VARCHAR(50) = '06-11-2019 00:00:00'


select case when convert(varchar, @A, 105) = @A then convert(datetime, @A, 120)  else convert(datetime, @A, 120)  end;
select case when convert(varchar, @A1, 105) = @A1 then convert(datetime, @A1, 105)  else convert(datetime, @A1, 105)  end;

Upvotes: 0

Marc Guillot
Marc Guillot

Reputation: 6455

Use a case to check if you have the first or the second format, and then call the convert function providing their exact format (105 for dd-mm-yyy and 120 for yyyy-mm-dd).

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15 

select case when substring(@A, 3, 1) = '-' then convert(datetime, @A, 105) 
            else convert(datetime, @A, 120) 
       end

Upvotes: 2

Soumya Ranjan Swain
Soumya Ranjan Swain

Reputation: 187

You can try this:

SELECT FORMAT(CONVERT(DATETIME,@A), 'yyyy-mm-dd hh:mm:ss.sss')

Upvotes: 0

Related Questions