question

DharmanDave avatar image
DharmanDave asked

How to store Historical dates in SQL Server ?

In case of SQL Server 2008 R2, and maybe earlier versions as well, the earliest date we're able to store is 1753-01-01. Is there any work around to store dates earlier than this ? Please advise. Thanks !
sql-server-2008-r2
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Blackhawk-17 avatar image
Blackhawk-17 answered
SQL Server 2008 introduced the **DATE** datatype. It's a 3 byte hit with a format of YYYY-MM-DD. You can store dates from 0001-01-01 to 9999-12-31.
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
+1. Seems to be the precice answer indeed. Datetime2 and datetimeoffset are other datatypes to be looked into if time part is needed as well.
0 Likes 0 ·
DharmanDave avatar image DharmanDave commented ·
Thanks Greg !! Helped a lot :) Cheers...
0 Likes 0 ·
Blackhawk-17 avatar image Blackhawk-17 commented ·
Glad it workewd out
0 Likes 0 ·
busynovadad avatar image
busynovadad answered
We store them in a dimension in our data warehouse as key value of "yyyymmdd", and then split the columns as date pieces. So, any row would have KeyVal Year Month Day 20130308 2013 03 08 20130307 2013 03 07
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.