Solved

Data being converted by dataset

Posted on 2011-09-14
12
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 55

Accepted Solution

by:
Huseyin KAHRAMAN earned 475 total points
ID: 36538509
convert your data to UTC
0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
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 55

Expert Comment

by:Huseyin KAHRAMAN
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 55

Expert Comment

by:Huseyin KAHRAMAN
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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

696 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