Convert a datetime column (currently of type nvarchar) to user-defined datetime format in SQL-CodePudding

In my SQL Server database table, I have a nvarchar column with values such as 'Thursday, 29 - September - 2022, 11:44 pm'.

I want to convert this to 'DD/MM/YY' format:

SELECT FORMAT ('Thursday, 29 - September - 2022, 11:44 pm', 'dd/MM/yy') AS date

and I'm expecting the result in DD/MM/YY format, but I'm not getting the proper output.

CodePudding user response：

You have to extract dd mmm and yyyy using substring first and then you can append those to be able to convert to date stype 106. After you have date object you can get any desired format.

Try following:

select CONVERT(varchar, CONVERT(DATETIME, 
               
               Substring(str, CHARINDEX(', ', str) 2, 2)   ' '    
               Substring(
  left(str, len(str) - CHARINDEX('-', reverse(str)) - 1), CHARINDEX('-', str)   2, 
  len(left(str, len(str) - CHARINDEX('-', reverse(str)) - 1)) - CHARINDEX('-', str))
                 ' '  
               Substring(str, len(str) - CHARINDEX('-', reverse(str))   3, 4)
               , 106), 3) 
  from (values ('Sun, 25 - May - 2022, 05:00 pm')) v(str)

Created a fiddle for demo: https://dbfiddle.uk/apcal_Ca

CodePudding user response：

You can fix the data in the string to be a valid date, then use the appropriate style of the convert function:

declare @d varchar(50) = 'Thursday, 29 - September - 2022, 11:44 pm';

select Convert(char(8), Cast(translate(Stuff(@d, 1, PatIndex('% %', @d), ''), '-,', '  ') as date), 3);

Demo DB Fiddle