Link to home
Start Free TrialLog in
Avatar of Stephen Forero
Stephen ForeroFlag for United States of America

asked on

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?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

<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?
Avatar of Stephen Forero

ASKER

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?
You need to check what the webservice is doing with the date.
With the info you provided we can only guess.
SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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.
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?
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.
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?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you everyone for your help, I will store the date time with datetimeoffset field like you suggested