Date conversion in SQL Server throws error

Question

I am trying to convert a date in mm/dd/yyyy format

select convert(date,'31/12/2013',101)

but I'm getting this error

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

How to do this? My system (Windows 7) has a dd-mm-yyyy format.. will system date format will have any impact on it?`

Answer 1

see

http://msdn.microsoft.com/en-us/library/ms187928.aspx

101 is the US style (mm/dd/yyyy)

try 103 (dd/mm/yyyy) which is British/French

SELECT CONVERT(DATE, '31/12/2013', 103)

Answer 2

Style 101 is the US style, so this has months first - your string represents the 12th day of the 31st month ....

What you need to use is style 103 (British/French) which uses the day first - so this string is 31st of December:

SELECT CONVERT(DATE, '31/12/2013', 103)

See the official MSDN SQL Server Books Online Documentation on CAST and CONVERT and what styles are defined and what they mean