Search code examples
sqlsql-serverdatabasecastingstring-conversion

String to Date type casting issue in SQL Server

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)

enter image description here

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)

enter image description here

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.

Solution

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

    enter image description here

    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