sql query returning date is off by 1 day when accessed through published web service

I am trying to do a simple query to my Azure Sql db and the date is coming back wrong by 1 day.
The weirdest thing is it is correct sometimes, but not all the time.

If I look in SSMS and do a SELECT * FROM MYTABLE.  I see the [filedate] column in question as 2015-08-19. (datatype is date)
THE CORRECT VARIABLE IS 2015-08-19

Now when I run the same query on my desktop program, which accesses a published web service I created, the returned data is 2018-08-18.
For some reason the date is being returned 1 day off.

To add another thing, when I run the web service locally, it returns the correct date.

Does anyone know why this is happening and how to fix this?
Stephen ForeroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Potentially stupid answer> Not without looking at the code behind the web service task.

<wild guesses>
The server date where the web services task is located is a day less than on your local box.
The table that you are accessing in SSMS has different values then the table that the Web Services task is accessing such that it returns a different date. Specifically if it depends on data loaded into these tables.
There's a parameter being fed to the web services task such that it is returning '2015-08-19'.
SELECT * FROM MYTABLE returns a set and you are trying to get a scalar value, so perhaps there is more than one row in the table the Web Services Task uses.
Oh I dunno.  Gremlins?
0
Stephen ForeroAuthor Commented:
the table I am accessing in web services is same table I am accessing in SSMS.
I have no parameters that are fed into web service.
The query is the exact same on SSMS and the web services.  SELECT * FROM MYTABLE.
It returns about 2000 rows, but the filedate column in question is the same for every row.

Would it matter where the server is?  I mean why wouldn't it be the same if I am just pulling a value from a table, not asking the time/date on the server?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to check what the webservice is doing with the date.
With the info you provided we can only guess.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
"I am trying to do a simple query to my Azure Sql db and the date is coming back wrong by 1 day.
The weirdest thing is it is correct sometimes, but not all the time.
"

time zone

sounds awfully like time zone to me

Is there a time zone difference between you and the server?
                 (e.g. is the server set to UTS and you are not)

Does the webservice assume the server time zone if the TZ isn't specified at input?
0
Stephen ForeroAuthor Commented:
thanks for following up guys.... I am still learning webservice and cloud db so I'm not really sure how or where the Microsoft Cloud Azure sql database handles time zones.  What are my options here if it is timezone issues?  Is there a way to specify and tell it to always report numbers in a certain time zone for example?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can always get the time zone from the SQL Server instance by running the SYSDATETIMEOFFSET command. With that you can build the date that you want by adding the difference from your time zone to the returned date from the webservice.
0
Stephen ForeroAuthor Commented:
I just ran this query on my local pc connected to the cloud db
select sysdatetimeoffset()

result is 2015-08-25 12:17:14.3435634 +00:00
I am in EST so right now it is 8:17... difference of 4 hours.  This may be a dumb question, but since I am using Microsoft Azure Cloud db, could it be 4 hours right now... then later on completely different because it is on a different service in a different time zone.  I guess this shows my lack of knowledge about cloud db.  Is it always one location or can it different locations?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not sure but I think all Azure servers are set to GMT so you can always take back 4 hours from your queries.
0
Stephen ForeroAuthor Commented:
so it is something like when I run the query I ALWAYS subtract 4 hours... or are there other options like when I post a time to the DB, I specify setting the timezone column to Eastern Standard Time?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you want to store a date with the time zone then you'll need a datetimeoffset field. This will solve your issue.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stephen ForeroAuthor Commented:
thank you everyone for your help, I will store the date time with datetimeoffset field like you suggested
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Azure

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.