CODEWITHSUNDEEP
sql-servert-sql
maitiest
maitiest

Reputation: 149

SQL Server : varchar to Date Conversion/Casting Error

If I use this

SELECT CONVERT(DATE, '26/03/2014', 101)

I get an error:

Conversion failed when converting date and/or time from character string.

But if I use this

SELECT CONVERT(DATE, '26/03/2014', 103)

There's no error and this is the result returned:

2014-03-26

I don't understand why the first code is not working, as far as I searched and understand is that 101 is for US date and 103 is for UK/French Date.

Upvotes: 3

Views: 1772

Answers (4)

dean
dean

Reputation: 10098

Use language neutral date representations for literals. In case with style 101, SQL Server assumed the MM/dd/yyyy instead of dd/MM/yyyy.

Here's a nice link with more info from MVP Tibor Karaszi:

http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172428

Thats becuase of the format specifier(101) which you are using.

101 is mm/dd/yyyy

So 26 cannot be a month. Hence resulting in error.

103 is dd/mm/yy

And hence it is working correctly. if the day would have been less than 13, it would have taken it as month and there would be logical error.

Upvotes: 3

Tauseef
Tauseef

Reputation: 2052

The convert signature is as follows CONVERT(data_type(length),expression,style)

for the date conversion, the styles are as follows

101 mm/dd/yy    USA

103 dd/mm/yy    British/French

more formats here http://www.w3schools.com/sql/func_convert.asp

Upvotes: 1

marc_s
marc_s

Reputation: 754468

This:

SELECT CONVERT(DATE, '26/03/2014', 101)

will be interpreted in the US way (mm/dd/yyyy) : the 26th month, 3rd day of 2014 - this obviously fails (no 26th month).

This however:

SELECT CONVERT(DATE, '26/03/2014', 103)

will be interpreted the European way (dd/mm/yyyy): the 26th day of the 3rd month (March) of 2014.

You need to very careful with parsing strings to date! Check out all the defined styles for CONVERT here

If you want to be sure it works always, use the ISO-8601 format: YYYYMMDD or in your case:

SELECT CAST('20140326' AS DATE)

will always work, no matter what language/regional settings you have

Upvotes: 4

Related Questions