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!

What version of SQL Server? 2008 introduced `datetime2` which supports earlier years. — Damien_The_Unbeliever, Jan 18 '12 at 10:27
*Why* can you not prevent the user from entering dates before 1753? Is it a potentially useful thing for your users to be able to do? — Jon Skeet, Jan 18 '12 at 10:31
You say that this is just a search form? In which case don't allow them to enter invalid dates which cannot possibly exist in the data. — Martin Smith, Jan 18 '12 at 10:48

score 1 · Accepted Answer · answered Jan 18 '12 at 10:41

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)

score 0 · Answer 2 · answered Jan 18 '12 at 10:30

0

I think there are 2 solutions, if you are working with sql server 2005

Keep dates in varchar field
Have different columns for year, month and day.

answered Jan 18 '12 at 10:30

narek.gevorgyan

4,165
5
32
52

score 0 · Answer 3 · answered Jan 18 '12 at 10:40

0

If you are using SQL Server 2008 you should use datetime2 datatype.

Even MSDN documentation recomends it!

answered Jan 18 '12 at 10:40

aF.

64,980
43
135
198

score 0 · Answer 4 · answered Jan 18 '12 at 10:45

0

How about creating a SQL CLR User defined Datatype? Using .NET we can save dates before 1753 as well.

Check out http://weblogs.sqlteam.com/mladenp/archive/2006/12/16/52754.aspx

answered Jan 18 '12 at 10:45

vmvadivel

1,041
5
7

score 0 · Answer 5 · answered Jan 18 '12 at 14:01

Depending on the actual format of your time component you can also try setting up specific fields for date components:

periodyear CHAR(4)
periodtype CHAR(2)
period CHAR(2)

This way you can store each segment of the date. For example you can do the following for "March 1745":

periodyear = '1745'
periodtype = '03' (month periodtype - arbitrary and up to you to define)
period = '03' (for 3rd month)

For days of the month you could try using the number of the day in the year or adding in a "day" field.

how to solve 1753 year issue in sqlserver?

5 Answers5

Linked