MST to CST Time Converson on columns

[Randy Bauer]

Hello,

Our web server and database server are hosted in the Mountain Standard Time Zone and we are in the Central Standard Time Zone.  The data is inserted and updated on the database in MST.  I want to display the data in the radGrid in CST.  Is there a function to convert or do I need to write something from scratch?  I need it to handle time changes also.  I was looking at the DataFormatString, but it doesn't look like that will work.  I'm thinking I need to do something on a per row basis.

Thanks,

Randy

[Schlurk]

I don't think there's a built in function to convert between the two time zones (within the Grid). Since the Grid is displaying the data you are binding to it (MST) I think you would have to convert this prior to binding it to the Grid.

[Chris]

Another option is to store your time stamps in Zulu time (GMT) and then convert based on the localization profile of the user.

Likewise, if stored in MST, queries could convert that to GMT then the web server can convert to local based on the localization profile.

[Randy Bauer]

Thanks for your help Chris and Schlurk,

I've done some testing with the following code(googled) and it appears to work.  I'm just not sure where/how to reference the code when I'm loading the data into the grid.  I'm thinking I need to do something in RadGrid1_ItemDataBound.

I don't believe I can do it from SQL.

    Function convertCST(ByVal dtDate As Date) As Date
        Dim starttime As Date
        Dim szone, ezone As TimeZoneInfo
        Dim soffset, eoffset, tzoffset As TimeSpan

        Dim id As String

        'dtDates = convertCST("01/01/2010 12:24 PM")

        starttime = dtDate

        id = "Mountain Standard Time"
        szone = TimeZoneInfo.Local

        ezone = TimeZoneInfo.FindSystemTimeZoneById(id)

        soffset = szone.GetUtcOffset(starttime)

        eoffset = ezone.GetUtcOffset(starttime)

        tzoffset = soffset - eoffset

        starttimestarttime = starttime + tzoffset

    End Function

Thanks,

Randy

[Veli]

Hi Randy,

Ideally, this code would go inside the NeedDataSource event of the grid, just before you set your grid's data source. For more info:

Advanced data-binding (using the NeedDataSource event)

So, you can:

1. Hook up to the event
2. Query your database
3. Loop over your data records
4. Convert your date fields
5. Assign the modified data source to RadGrid.DataSource

All the best,
Veli
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Randy Bauer]

Hi Veli,

Thanks for your response.  Below is the code I put in it ItemDataBound Event and it appears to be working.  Just not sure that it is the best place to put it.  Would it be better in the NeedDataSource?  I want to make sure I am getting the best performance also.

Protected Sub RadGrid1_ItemDataBound(ByVal sender As Object, ByVal e As Telerik.Web.UI.GridItemEventArgs) Handles RadGrid1.ItemDataBound
        Try
            'Convert database MST to CST for end user
            If TypeOf e.Item Is GridDataItem Then
                Dim item As GridDataItem = DirectCast(e.Item, GridDataItem)
                Dim dtModifiedDate As Date

                If IsDate(item("modifiedDate").Text) Then
                    dtModifiedDate = convertCST(item("modifiedDate").Text)
                    item("modifiedDate").Text = dtModifiedDate.ToString("MM/dd/yyyy hh:mm tt").Trim
                End If
            End If

            If "matter".Equals(e.Item.OwnerTableView.Name) Then
                If TypeOf e.Item Is GridEditFormInsertItem AndAlso e.Item.OwnerTableView.IsItemInserted Then
                    Dim insertItem As GridEditFormInsertItem = DirectCast(e.Item, GridEditFormInsertItem)

                    Dim ddlStatusList As DropDownList = DirectCast(insertItem.FindControl("ddlStatus"), DropDownList)
                    ddlStatusList.DataSource = GetStatusList()
                    ddlStatusList.DataBind()

                    ddlStatusList.SelectedValue = 1

                    Dim ddlPriorityList As DropDownList = DirectCast(insertItem.FindControl("ddlPriorityDesc"), DropDownList)
                    ddlPriorityList.DataSource = GetPriorityList()
                    ddlPriorityList.DataBind()

                    ddlPriorityList.SelectedValue = 2

                End If

                If (TypeOf e.Item Is GridEditableItem AndAlso e.Item.IsInEditMode) Then
                    Dim item As GridEditableItem = e.Item

                    Dim ddlStatusList As DropDownList = DirectCast(item.FindControl("ddlStatus"), DropDownList)
                    ddlStatusList.DataSource = GetStatusList()
                    ddlStatusList.DataBind()

                    If (Not Session("updatedStatusValue") Is Nothing) Then
                        For Each ddlItems As ListItem In ddlStatusList.Items
                            If ddlItems.Text = Session("updatedStatusValue") Then
                                ddlStatusList.SelectedValue = ddlItems.Value
                            End If
                        Next
                    End If

                    Dim ddlPriorityList As DropDownList = item.FindControl("ddlPriorityDesc")
                    ddlPriorityList.DataSource = GetPriorityList()
                    ddlPriorityList.DataBind()

                    If (Not Session("updatedPriorityValue") Is Nothing) Then
                        For Each ddlItems As ListItem In ddlPriorityList.Items
                            If ddlItems.Text = Session("updatedPriorityValue") Then
                                ddlPriorityList.SelectedValue = ddlItems.Value
                            End If
                        Next
                    End If
                End If
            End If

        Catch sqlEx As SqlException
            DisplayErrorPage(sqlEx.Message)

        Catch ex As Exception
            DisplayErrorPage(ex.Message)

        End Try
    End Sub

Function convertCST(ByVal strLastLogin As String) As Date
        Dim StandardTimeZone, MountainTimeZone As TimeZoneInfo
        Dim StandardTimeOffset, MountainTimeOffset, timeZoneOffset As TimeSpan
        Dim mst As String = "Mountain Standard Time"
        Dim cst As String = "Central Standard Time"

        Try
            'standardTimeZone = TimeZoneInfo.Local 'We don't want local time because the web server is in MST also.
            standardTimeZone = TimeZoneInfo.FindSystemTimeZoneById(cst)
            MountainTimeZone = TimeZoneInfo.FindSystemTimeZoneById(mst)

            Dim dtLastLogin As DateTime = strLastLogin

            standardTimeOffset = standardTimeZone.GetUtcOffset(dtLastLogin)
            MountainTimeOffset = MountainTimeZone.GetUtcOffset(dtLastLogin)

            timeZoneOffset = standardTimeOffset - MountainTimeOffset

            convertCST = dtLastLogin + timeZoneOffset

        Catch ex As Exception
            Throw

        End Try
    End Function

[Veli]

Hello Randy,

The approach you have taken is also correct. There is an important difference, however. Note when you use ItemDataBound, RadGrid binds to the original data source. As a result, any grouping/sorting/filtering and other data operations applied on RadGrid's data source will use the MST DateTime format. Effectively, you are only changing the string representation of your DateTime values. RadGrid works with the original. This is neither better, nor worse, you just have to have it mind.

On the other hand, there is the NeedDataSource approach. Note that in this case RadGrid is only aware of your converted CST DateTime values. All internal operations are carried out on the converted dates. This means that you do not need to make any effort for making sure any date your user selects (for filtering or editing) is converted to the right format. In the former case (ItemDataBound), users would enter dates that you would later need to convert before passing to the grid, because users expect your dates to be in CST, while RadGrid works with MST dates. Practically looked at, I would recommend NeedDataSource for converting your dates.

There are no additional performance considerations. In either case you would need to get each date value and convert it. However, there might be the slight performance advantage of not having to worry (ie. convert) dates your users enter on filtering or editing with the NeedDataSource approach.

Best wishes,
Veli
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Randy Bauer]

Hi Veli,

Thank you very much for your insight.  I agree and will switch it to use the NeedDataSource.

Thanks,  Randy