The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

Question

I have the below query, giving an error that The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

INSERT INTO ACCount (DocumentNo, AccNo, DueDate, [Description], DC, Amount, Amount1, Amount2, DateCreated, DateModified, [Type]) VALUES (40, 6000012, '14/12/2012 12:00:00 AM', N'', N'C', 365.70, 551292.750000000, 365.700000000, '14/12/2012 12:00:00 AM', '14/12/2012 12:00:00 AM', N'PI')

Unable to know what exactly is the error.

How could i resolve this?

Thanks in advance.

score 2 · Answer 1 · answered Dec 14 '12 at 11:24

'14/12/2012' is the 12th day of the 14th month on most servers. You would have to use the correct format specification to represent the dates, or better (but only in application code) : only pass it down as a parameter, using the natural representation of the date in the calling code (then there is never any string representation, so it is never ambiguous).

Another way of making it very unambiguous both to the server and the human reader is something like:

'14 Dec 2012 12:00:00 AM'

score 0 · Answer 2 · answered Dec 14 '12 at 11:25

You're using an ambiguous datetime format, and the defaults for your regional settings mean that it's not accepting it. Try:

INSERT INTO ACCount (DocumentNo, AccNo, DueDate, [Description], DC, Amount,
       Amount1, Amount2, DateCreated, DateModified, [Type])
VALUES (40, 6000012, '2012-12-14T12:00:00 AM', N'', N'C', 365.70, 551292.750000000, 
        365.700000000, '2012-12-14T12:00:00 AM', '2012-12-14T12:00:00 AM', N'PI')

Of course, given that the time components are all midnight, you may prefer to just use 20121214 (which is an unambiguous date only format)

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

2 Answers2