Conversion failed when converting datetime from character string.

“If we mean to have heroes, statesmen and philosophers, we should have learned women.” - Abigail Adams

Adams was born 3 days before Christmas, December 22, 1744.

I was trying to insert her date of birth into an MSSQL DataTime field, similar to this:

   1: insert in to firstlady
   2: (dateofbirth) values
   3: ({d '1744-12-22'})
Show/Hide Line Numbers . Full Screen . Plain

It returned the following error message:
“[Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting datetime from character string.”.

It seems an MSSQL DataTime field can hold dates only after the year 1753. So much for Abigail Adams. At this point, the only option left is to split the date, month, and year into three separate fields and store them in three different columns.

1 Comment :
Monday 07 November 2011 10:37 AM
What are you using, 2005? 2008 and 2008R2 offers a datetime2 datatype that supports years in the range 0001 - 9999, if upgrading is an option for you. If not, well, yeah, pretty sucky decision on Microsoft's part.