[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Data being converted by dataset

Posted on 2011-09-14
12
Medium Priority
?
311 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 60

Accepted Solution

by:
HainKurt earned 1900 total points
ID: 36538509
convert your data to UTC
0
 
LVL 60

Expert Comment

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

Assisted Solution

by:lludden
lludden earned 100 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 60

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 60

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 60

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

650 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