Problem solve Get help with specific problems with your technologies, process and projects.

'Out-of-range datetime value' error in SQL Server when working with dates

Have you ever been stumped by this error message: "conversion of char data type to datetime data type resulted in an out of range datetime value"? Contributor Serdar Yegulalp shares causes and fixes for the frequently seen error message in SQL Server.

One of the more underrated issues that I run into a great deal with SQL Server is what happens when you pass data...

to SQL Server that hasn't been properly sanitized for use by the front end. The most extreme consequence of this sort of thing is, of course, a SQL injection attack. But there are other things that can go wrong, not all of them obvious.

Here's one error that stumps a lot of people because they don't realize the problem is due to unsanitized input. The message is: 'Conversion of char data type to datetime data type resulted in an out of range datetime value.' And they get it sometimes when passing a date value to a stored procedure or user-defined function (or even in a dynamically-created SQL string). At other times, if they pass a date value for the function or SP directly to SQL Server -- for instance, by using the Query Analyzer -- it works fine.

As it turns out, here are a couple of things that may be wrong:

  • The locale settings for the server may be wrong. When you configure Windows Server (and in turn SQL Server) for a specific locale, the formatting of dates and times are inherited automatically from those settings. If the locale settings don't match the data being passed to the server -- for instance, if dates are passed as MM/DD/YYYY when they should be DD/MM/YYYY -- then the error will result. It can also happen if you're trying to import data from a source where the date formatting clashes with your locale settings.
  • There's a locale mismatch between the front end and back end. This typically happens if you have the front end (the Web server) and the back end (the data server) running on entirely separate machines and the locale settings on the two machines do not match.
  • The front end is not properly parsing dates. This can happen if you have date values being passed to SQL Server from a front-end application, typically a Web page generated by ASP or Perl, that are not validly formatted either. If you accept a date format from the user without parsing it as a date format and then pass it on to SQL Server, there's no guarantee it'll be accepted.

Here's one way to get around that: If you're responsible for how the front end works, parse the dates as rigidly as possible. Make users select dates from a static list or

Get more on SQL Server:
  • SQL Server INSIDER: The ezine with tips for SQL Server pros

have separate input fields for day, month and year so they can be parsed separately. (This last method can be annoying to program, but it's almost completely foolproof.) If you're forced to obtain a date from a single text field, supply a specific formatting example for the user, e.g.: "Enter a date in the format MM/DD/YYYY, and make sure the results are a locale-valid datetime value before passing them on to SQL Server. Finally, if you're not responsible for how the front end works, the most you can do is attempt to verify that the passed date value is validly formatted using CONVERT or CAST, and raise an error if it isn't.

Serdar Yegulalp
has been writing about Windows and related technologies for more than 10 years and is a regular contributor to various sections of TechTarget as well as other publications. He hosts the Web site, where he posts regularly about Windows and has an ongoing feature guide to Vista for emigrants from Windows XP.
Copyright 2007 TechTarget


Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning