String to Date type casting issue in SQL Server-CodePudding

I am trying to typecast the string data to date but it works with some date but not for others. I am using the convert() function

Example

When I give this value to the string parameter it works fine

select  Convert(DATE, '01/05/2017', 101)

but trying the same code with a different but correct date doesn't work and gives the below-shown error

select  Convert(DATE, '13/06/2013', 101)

I am getting the following error:

Msg 241, Level 16, State 1, Line 15 Conversion failed when converting date and/or time from character string.

CodePudding user response：

As @Squirrel mentioned in the comments the right style is 103 , from the docs

103 British/French dd/mm/yy

select  Convert(DATE, '01/05/2017', 103) as my_date;
my_date
2017-05-01

select  Convert(DATE, '13/06/2013', 103) as my_date2;

my_date2
2013-06-13

I will suggest never store dates as text, you can create another column with datetime (even though this is out of the question scope) and update the column like the example below:

create table test (
wrong_date_format varchar(25)
);

insert into test values 
('01/05/2017'),
('13/06/2013');

ALTER TABLE test ADD wright_date_format date;

update test set wright_date_format = Convert(DATE, wrong_date_format, 103);

Demo

CodePudding user response：

@Muhammad Waheed Please check this might be helpful to u..

select  Convert(DATE, Cast('13-Jun-2013' as date), 101)

because SQL Server is considering 13 in your second exemple as a month. that's why causing error.