I quite often receive questions where users are looking for solution to following error:

Microsoft OLE DB Provider for SQL Server error ’80040e07′
Syntax error converting datetime from character string.

OR

Microsoft SQL Native Client error ’80040e07′
Syntax error converting datetime from character string.

If you have ever faced above error – I have a very simple solution for you.

The solution is being very check date which is inserted in the datetime column. This error often comes up when application or user is attempting to enter an incorrect date into the datetime field. Here is one of the examples – one of the reader was using classing ASP Application with OLE DB provider for SQL Server. When he tried to insert following script he faced above mentioned error.

INSERT INTO TestTable (ID, MyDate)
VALUES (1, '01-Septeber-2013')

The reason for the error was simple as he had misspelled September word. Upon correction of the word, he was able to successfully insert the value and error was not there. Incorrect values or the typo’s are not the only reason for this error. There can be issues with cast or convert as well. If you try to attempt following code using SQL Native Client or in your application you will also get similar errors.

SELECT CONVERT (datetime, '01-Septeber-2013', 112)

The reason here is very simple, any conversion attempt or any other kind of operation on incorrect date/time string can lead to the above error. If you not using embeded dynamic code in your application language but using attempting similar operation on incorrect datetime string you will get following error.

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

Remember: Check your values of the string when you are attempting to convert them to string – either there can be incorrect values or they may be incorrectly formatted.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL DateTime, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology