Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Question Unanswered: URGENT: Problem with DATETIME

    Hello People,

    I have urgent problem here. In the database there is a datetime field: 4004-09-13 00:00:00.000
    It reprecent the real date + 2000 years, because DATETIME in SQL Server starts from year 1753 and I need to store dates from 1500. How I can take the real date just with SELECT, no StoredProc and stuff like that.

    Thanks.

  2. #2
    Join Date
    Mar 2004
    Posts
    114
    If possible in your application, you should store date in a char(8), like YYYYMMDD.

    Personally, it has solved me many problems

  3. #3
    Join Date
    Jan 2004
    Posts
    30

    Unhappy

    Quote Originally Posted by kuzco
    If possible in your application, you should store date in a char(8), like YYYYMMDD.
    Yep, I know, but this is not possible, because this data is send later to many clients as monthly updates. I tryed YEAR(DATE_FIELD), MONTH(DATE_FIELD) and DAY(DATE_FIELD). So this way I can remove 2000 years, but how to join them in data again within the SELECT statement...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dateadd("y",-2000,fakeyear)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Posts
    30
    r937, thanks man. DATEADD works fine.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oops, i gave you the microsoft access syntax

    sql server is dateadd(year,-2000,fakedate)

    have you really tested it?

    because it's not going to give you dates below 1753, you know

    it gives this error: "Adding a value to a 'datetime' column caused overflow. (State:22008, Native Code: 205)"

    i think you're just gonna have use YEAR(fakedate)-2000 and then concatenate a string with MONTH(fakedate) and DAY(fakedate)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Posts
    30
    I tried it with double "y".
    dateadd("yy",-2000,fakeyear)

    As I told you - it works. I just needed the idea. Thanks again man.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by The-Saint
    I tried it with double "y".
    dateadd("yy",-2000,fakeyear)

    As I told you - it works. I just needed the idea. Thanks again man.
    what values did you test it on?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2004
    Posts
    30
    select top 100 d.d7, dateadd("yy", -2000, d7) from d

    this outputs:

    3999-07-26 00:00:00.000 1999-07-26 00:00:00.000
    3999-03-16 00:00:00.000 1999-03-16 00:00:00.000
    3999-03-16 00:00:00.000 1999-03-16 00:00:00.000
    ...................

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dude, if you're not gonna go below 1999 then you need not have added 2000 to your dates to begin with

    try subtracting 2000 years from 3749-01-01

    test, test, test
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by The-Saint
    Hello People,

    I have urgent problem here. In the database there is a datetime field: 4004-09-13 00:00:00.000
    It reprecent the real date + 2000 years, because DATETIME in SQL Server starts from year 1753 and I need to store dates from 1500. How I can take the real date just with SELECT, no StoredProc and stuff like that.

    Thanks.
    I'm just curious, but how do you plan to handle the calendar reformations? I'd be really nervous due to the fact that Sept 1 1752 in some places is almost two weeks earlier than it is in other places! Unless you have some way to deal with these problems, your dates will be suspect!

    -PatP

Posting Permissions

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