Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    34

    Unhappy Unanswered: Date / Time Field

    Dear Frends
    Iam using SQL Server 2000
    Is there any way to use Date time field to store value before the Date 01-Jan-1753?
    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Yes. Go to Books Online and, at the index tab, type in "two digit year cutoff option". It tells you how to change it there.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Aug 2003
    Posts
    34

    Unhappy Date Time Limits on SQL

    I tray to Configure SQL server using the above "tow degits year cutoff" option to go before the year 1753, But I can't
    Could you please help me

    Thanks
    Last edited by ridwan; 12-11-05 at 02:02.

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Give me a walkthrough of how you tried to change it, including script if you used one.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't think you can store a date before 1 Jan 1753 in SQL Server. The DATETIME datatype only stores "when", but in order to correctly store dates before the Gregorian calendar reformation you also need to know "where" the date was recorded. This is because dates like June 1, 1750 were observed over a two week period depending on where it was observed, for example it was observed much earlier in France than in England.

    For more details on this problem, see the article in the Wikiedia.

    -PatP

  6. #6
    Join Date
    Aug 2003
    Posts
    34

    Red face

    I have tried the following in SQL Query Analiser (Master DB)

    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    sp_configure 'two digit year cutoff', 1751
    GO
    RECONFIGURE
    GO


    it gives the message
    '1751' is not a valid value for configuration option 'two digit year cutoff'.

    Thank you

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You can't do it at all. I just did a little test to confirm. Even if you do get it to recognize earlier years, when you try to input a date earlier than 1753, you will get a char conversion error. I would be happy to learn something different from MS, but I don't believe what you are wanting to do is possible at all with datetime.

    This is validated by Books Online. See below quote:

    Remarks
    Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

    The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •