Posted by Plamen Ratchev on 04/09/07 17:01

Without having your table structure and sample data, it is a bit difficult
to troubleshoot, but a few notes that can help you:

- Based on the style 103 that you use for the CONVERT function, seems you
are converting date stored as string in format "dd/mm/yyyy" to a datetime
type. The error that you get indicates that the date string cannot be
converted, because the day, month, or year portion is out of the allowed
range. You can easily simulate the error if you run something like this:
SELECT CONVERT(datetime, '23/13/2007', 103). The month cannot be 13 so it
fails with the error you get. You can use the LEFT, RIGHT and SUBSTRING
functions to extract the day, month, and year portion of both columns
(Placement_Date and Discharge_Date) and check for invalid values, then clean
your data. For year the down side value is 1753 (that is the lower limit for
datetime data type).

- It is always best to keep date values in columns of datetime data type.
That way you do not have to worry about the format and can benefit of using
all datetime functions with no need to convert.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]