how to solve 1753 year issue in sqlserver?

Question

I am using sqlserver to store/search. The problem is I have a form which takes start date and end date to search. If I enter dates after 1753 then no problem. If I enter any date below 1752, I am getting the below exception:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. 

I know sql server supports date range b/w 1753 and 9999. But I cannot prevent the user from entering dates below 1753. Please help me.

Thanks!

Answer 1

If you need to store dates before 1753 in SQL Server 2005, then you're going to have to store it as text.

If you're storing the date only (no time component), then I'd recommend:

CREATE TABLE Tab (
    /* Other Columns */,
    FunnyDate char(8) not null,
    constraint CK_FunnyDate_MostlyValid CHECK (
        FunnyDate like '[0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9]')
    /* Other constraints */
)

Which should force it to closely resembly a valid date (in YYYYMMDD format). You might also consider adding a computed column, which represents it as a datetime, if it's convertible to such a value.

If you need to include a time component, I'd recommend storing it in YYYY-MM-DD"T"hh:mm:ss format, and again enforced by a check constraint.

The formats I've recommended above are safely convertible to datetimes without having to consider regional settings.

You can also write stricter check constraints, if needed (e.g. the above does allow a 14th month, for example)