VARCHAR to DATE

[djj55]

Hello, 2008R2
I have a VARCHAR column with a date as DD/MM/YYYY. I want to convert to DATE datatype. I tried

declare @D date = '21/1/2015'; 
select CONVERT(date, @D, 103);

but get the error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

I know there is a command to change default date format but cannot remember it.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Olaf Doschke]

Your specific case works for me, but what about records having NULL or a string not fitting that date format? The varchar field might have US mm/dd/yyyy instead of mm/dd/yyyy

Bye, Olaf.

 

[djj55]

Thank you for the response.
I have to assume no US and have taken care of the NULL and bad formats.
Client provided a .CSV file with DD/MM/YYYY in the column (example 27/03/2015) so I have to assume non US.

What I ended up doing is separating the parts and putting them back together to cast as DATE.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Olaf Doschke]

Bulk loading CSV differs from querying a table. What do you use? Format file? bcp, bulk insert?

Bye, Olaf.

 

[djj55]

I used the import wizard from SSMS, so no problem bringing in the data. I did let it default to a varchar for the dates. Thus my VARCHAR to DATE question.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Olaf Doschke]

OK, then you should ask yourself if the date format you found in CSV was really also put into the varchar field. The SSMS process might have transposed it already.

Bye, Olaf.

 

[yelworcm]

The command to change the dateformat would be

set dateformat dmy

Should take effect for the duration of the session.