Time Zone Problem in Web Service

Question

Hi All,

I Am Using Vb.net and xml webservice.My Problem Is When i  Call dataset
Using Web service  .The Date  THAT IS Getting Is Differnent That was in
database(Sql) If I Change my Time zone.I Have Web service in The Live
Server .And My Client Application using vb.net .If I change the Time
Zone in Client (PDA) There Value of Data is changing.

My Code in Web service
<WebMethod()> _
    Public Function GetDS(ByVal strSQL As String) As DataSet
        Dim dt As New DataTable()
        Dim a(0) As String
        Dim dr As DataRow
        Try
            If conGlobal.State = ConnectionState.Closed Then
conGlobal.Open()
            'conGlobal.Open()
            DS.Reset()
            DA = New SqlDataAdapter(strSQL, conGlobal)
            DA.Fill(DS)
            DA.Dispose()
            conGlobal.Close()
            DS.Locale =
System.Globalization.CultureInfo.InvariantCulture
            Return DS
        Catch ex As Exception
            dt.Columns.Clear()
            dt.Columns.Add("Error")
            dr = dt.NewRow
            dr.Item("Error") = ex.Message.Trim
            dt.Rows.Add(dr)
            DS.Tables.Add(dt)
            'DS.Tables.Add("Error").Rows.Add(ex.Message.Trim)
            conGlobal.Close()
            Return DS
        End Try
    End Function

In VB.net
 DsPR = GetDS("SpGetLastPostedDate '" & Trim(cmbExchange.Text) & "'," &
Val(GintCompanyID) & " ")

Acutal Data In Table is 21-10-1980 and it come as 20-10-1980 and time
lbLastPostedDate.Text = dr.Item("TranDate") '20-10-1980 'Error

Answer 1

Hi I went through this issue a little while ago. Sql server will not do any date conversions for you.

http://social.msdn.microsoft.com/Forums/en-US/vsdebug/thread/260a2936-7a4d-4a65-aa25-d70a0d0728d0/

Basically, you must be careful where the web service is hosted, as it will convert dates for you automatically.

Is the live server the same timezone as sql ? Is your client in a different timezone than the web service. Did you restart your client after changing the timezones ?

When your client starts, it stores the timezone, as does the web service when it starts.

Let me know my previous post helped.

Answer 2

On another note, if you do not want any conversion to be done at all, I suggest you edit the data access to format the date field stored in SQL server.

in T-SQL

CONVERT(varchar(24), InvoiceDate, 7) AS  InvoiceDate

This will return 'Dec 15, 08' instead of '2008-12-15 08:45:29.053 '

Answer 3

You can take a look at: http://weblogs.asp.net/razan/archive/2009/10/31/internationalization-in-the-web-service-method-of-asp-net.aspx

---

Razan