Sql Server Dates sending me nuts

[big_al]

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.

[r937]

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?

[big_al]

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?

[r937]

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?

[big_al]

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

[r937]

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?

[big_al]

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

[danfran]

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!