 |
|
| SQL Server Tips: |
|
|
|
DATABASE ADMINISTRATION
'Out-of-range datetime value' error in SQL Server when working with dates
By Serdar Yegulalp
05.14.2007
Rating: -2.00- (out of 5)
|
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 m
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
ismatch 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 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.
[TABLE]
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
