SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard big_al's Avatar
    Join Date
    May 2000
    Location
    Victoria, Australia
    Posts
    1,661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sql Server Dates sending me nuts

    Hi

    I have a very basic table that has a LastModifed date field, of type SMALLDATETIME.

    Now the problem is, for some ODD reason when ever I insert a record, the resulting date is ALWAYS 2 days in the future, regardless!

    I have tried getdate() (my prefered option usualy), tried setting the date in my app, have even tried setting it to two day in the past, but still results in a record with a date stamp of 2 days in the future (of today)

    I control the server and have gone over every possible setting I could think of, everything is running fine, I have run querys in QA both localy (connected to the server) and on the server it's self, both using normal getdate and Convert(smalldatetime, getdate()) and all give the CORRECT date.

    Can anyone shed some light why this would be happening?

    Thanks in advance.
    .NET Code Monkey

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you say when you insert a date it is ALWAYS 2 days in the future

    then you say you run queries in QA and all give the CORRECT date

    so, what exactly is the problem again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard big_al's Avatar
    Join Date
    May 2000
    Location
    Victoria, Australia
    Posts
    1,661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    What I mean, the date is always inserted as being 2 days in the future, NOT BY ME, I am trying to insert the current date, but ends up being 2 days in the future, for some unknown reason...

    But when I try getdate() in QA, it gives me the correct date, so the server is obviously aware what the current date is, what I do not know is why the resulting values are always 2 days in the future...

    Both the Sql Servers Server time is set to the correct date and the web servers date is the same too..

    I have not experienced this behavior before, on the same servers, or on other servers.

    Any ideas?
    .NET Code Monkey

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sloppy application code?

    you say all the servers have the right date, and when you do it through query analyzer it works okay

    where exactly do these future dates come from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard big_al's Avatar
    Join Date
    May 2000
    Location
    Victoria, Australia
    Posts
    1,661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    I have checked a re-check the code, and it is fine.

    Even if I would hard code a date of 2005-01-19 00:00:00, I get 2005-01-21 00:00:00

    I have also put getdate() inside the query as a value member, with the same result....

    It has me completly baffled
    .NET Code Monkey

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    where exactly are you hardcoding this date? can i see the query?

    now, one day's difference i could understand, since it is a well-known fact that you guys in Oz are a day behind, so maybe the servers are trying to adjust for GMT time zone or something

    but two days?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard big_al's Avatar
    Join Date
    May 2000
    Location
    Victoria, Australia
    Posts
    1,661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Deleting the table and re-creating it has fixed the problem.....

    The database was created by another developer (badly), not sure if it may have been some odd design feature.. but I did not find any default values or rules for this field...

    Ahh well, maybe this belongs in the "Gremlin" category :P
    .NET Code Monkey

  8. #8
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Funny... I've seen this sorta behavior before.. Maybe it's just folklore.. maybe not.

    We were using SQL to keep track of site visitors and something weird like their end-time came before their start-time -for just a group of users.. Really strange..

    We checked dates everywhere and couldn't find the answer!


Bookmarks

Posting Permissions

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