I am trying to convert a character string in T-SQL to a date. I cannot change the format as this is hard coded in program where this information come from. My character string format is dd/mm/yyyy Example 08/01/2002

When I use code: CONVERT(char(10), TRY_CAST([Birth Date] As Date), 103) As [DOB] it returns mm/dd/yyyy Example 01/08/2002

Of course when data is 31/01/2005 the code returns NULL .

Any help is much appreciated.

CodePudding user response：

select FORMAT(convert(date,  '31/01/2005', 103), 'dd/MM/yyyy') as dte

Fiddle