Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

The minimum date range is set to somewhere in the 1750's. I need to store dates prior to 1750. One solution that came to mind is to store the dates in a particular format in a 'varchar' field, and later on, retrieve them back to the UI as we want.

Is there a better solution to this - an elegant one?

share|improve this question
You really shouldn't contemplate storing dates as strings in a database. It's one of the largest sources of bugs, in my experience. – Damien_The_Unbeliever Aug 24 '12 at 9:12
That's exactly what I had in mind. Format mismatches are bound to happen. Creates liabilities for a developer - which is bad. – KovidR Aug 24 '12 at 9:25
If memory serves me right, this goes back to the change from the julian calendar to the gregorian Calendar in 1752. The 1st jan was the first full year after the change hence the reason for it being selected. Picking dates prior to the change in calendars is fraught with complications. – GrumpyMonkey Aug 26 '12 at 18:17

migrated from stackoverflow.com Aug 26 '12 at 9:05

1 Answer

up vote 5 down vote accepted

A datetime2 goes back further - http://msdn.microsoft.com/en-us/library/bb677335.aspx - to 0001. If you want to go back further still, then a custom data type may be an option, although given the potential confusion and lack of precision even before 1753, it is doubtful how precise you could expect to be.

share|improve this answer
I am working on a previously built client database. I was trying to find a solution that would not require a database restructuring. I'd now have to regenerate the third-party DAL after restructuring the database. It may sound lazy, I hadn't used 'datetime2' till date. Thank you for the swift response. – KovidR Aug 24 '12 at 9:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.