Solved

Data being converted by dataset

Posted on 2011-09-14
12
302 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

930 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

9 Experts available now in Live!

Get 1:1 Help Now