Solved

Data being converted by dataset

Posted on 2011-09-14
12
301 Views
Last Modified: 2012-06-22
I have a web service that is returning a dataset. The dataset has a DateTime column. If I call the webservice by hand like "http://localhost:1349/Service1.asmx/RetrieveInvoiceRows" I get a record that looks like this.


<WorkBookRow diffgr:id="WorkBookRow1" msdata:rowOrder="0">
  <ID>24481</ID>
  <WorkBookID>d091162b-ea35-4636-b398-5edd50fe5f84</WorkBookID>
  <Status>2</Status>
  <Row>1</Row>
  <VendorCd>100000</VendorCd>
  <VendorName><Unknown Vendor Code></VendorName>
  <InvoiceNumber>KEVIN41</InvoiceNumber>
  <JDE_Account_Code>950913510.69055.1001</JDE_Account_Code>
  <SubLedger />
  <SubLedgerType />
  <PaymentCheckRemark>2011-09-13 00:00:00</PaymentCheckRemark>
  <Amount>1.0000</Amount>
  <InvoiceDate>2011-09-13T00:00:00-04:00</InvoiceDate>
  </WorkBookRow>

However, when I make a call to the same web service from my client machine I am getting this!
<ID>24481</ID>
  <WorkBookID>d091162b-ea35-4636-b398-5edd50fe5f84</WorkBookID>
  <Status>2</Status>
  <Row>1</Row>
  <VendorCd>100000</VendorCd>
  <VendorName><Unknown Vendor Code></VendorName>
  <InvoiceNumber>KEVIN41</InvoiceNumber>
  <JDE_Account_Code>950913510.69055.1001</JDE_Account_Code>
  <SubLedger />
  <SubLedgerType />
  <PaymentCheckRemark>2011-09-13 00:00:00</PaymentCheckRemark>
  <Amount>1.0000</Amount>
  <InvoiceDate>2011-09-12T23:00:00-05:00</InvoiceDate>
  </WorkBookRow>

That the EXACT same record just viewed first from the server and next saved to disk via DS.writeXML on my client PC. It is like the dataset is changing the date from my client machine.

Note: The server is in eastern timezone and my client PC is in central. Perhaps that is affecting this.

My question how is the date changing and how do I prevent it?



0
Comment
Question by:TCBailey
  • 5
  • 4
  • 3
12 Comments
 
LVL 51

Accepted Solution

by:
HainKurt earned 475 total points
ID: 36538509
convert your data to UTC
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36538512
or convert datetime column to varchar/string on server
0
 
LVL 18

Assisted Solution

by:lludden
lludden earned 25 total points
ID: 36538583
2011-09-13T00:00:00-04:00
is the same as
2011-09-12T23:00:00-05:00

Timezone moves over by one and offset increases.  Both should convert to the same value.
0
 

Author Comment

by:TCBailey
ID: 36538587
HainKurt,

Yes, I do see what is happening. It is a timezone issue.
I think it best convert the data because I will have multiple zone clients. You said, "convert your data to UTC". Do you mean, "convert your stored data to UTC" and then reconvert for each client as it is pulled back down?
0
 

Author Comment

by:TCBailey
ID: 36538601
Ah, Lludden, That does clear it up. I just save it from where ever. Then the service is converting it for me on the way back down. I just have to convert it on the client side when I pull it back.
True?
0
 

Author Comment

by:TCBailey
ID: 36538639
Sorry, I'm still not getting this. I orginally sent the data from my client PC to the service. The web service saved it to the database without any conversion. (The client machine sent "2011-09-13 00:00:00" to the web service) If the service was converting then it would have stored "2011-09-12 20:00:00" and reconverted back to "2011-09-13 00:00:00" when I pulled it back down.
So I'm thinking my last to lLudden isn't correct.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 51

Expert Comment

by:HainKurt
ID: 36538751
in your web service i guess you used a datetime variable..... make it string and it should be ok...
0
 
LVL 18

Expert Comment

by:lludden
ID: 36538827
SELECT  DATEDIFF(n,cast('2011-09-13T00:00:00-04:00' as DATETIMEOFFSET ), cast('2011-09-12T23:00:00-05:00' AS DATETIMEOFFSET ))

0

Look at it this way.  If it is midnight in New York, it is 11pm the previous day in Chicago.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36538868
in your web service probably you are calling a sql


like

select ...., InvoiceDate,... from ... where ...

-->


select ...., cast(InvoiceDate as varchar) as InvoiceDate,... from ... where ...
0
 
LVL 18

Expert Comment

by:lludden
ID: 36538893
If you want to strip off the UTC codes and get local times

SELECT      CAST(SWITCHOFFSET('2011-09-13T00:00:00-04:00', '-05:00') as datetime),
            CAST(SWITCHOFFSET('2011-09-12T23:00:00-05:00', '-05:00') as datetime)

2011-09-12 23:00:00.000      2011-09-12 23:00:00.000

Replace the '-05:00' to whatever your local time zone is
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36538896
or instead of casting use convert and use 120 which will convert it to "YYYY-MM-DD HH:MI:SS"

convert(varchar, GETDATE(),120)
2011-09-14 16:07:51

for all other options, refer to

http://www.sqlusa.com/bestpractices/datetimeconversion/
0
 

Author Closing Comment

by:TCBailey
ID: 36538909
HainKurt,

You did it. The problem was BOTH on the server side storage and the need to for UTC.

I didn't realize it but it was stripping out the times before being stored in the database. The storage problem combined with my lack of understanding of the need to convert to UTC caused me not to catch it.

Thanks guys,
TcB
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now