Solved

Data being converted by dataset

Posted on 2011-09-14
12
303 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

773 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