none
Date conversion

    Question

  •  

    Hi,

    I need to convert dates stored in my table/view from Epoch UTC format to local time. Problem is, when I convert from Epoch to local time in my SQL, it doesn’t handle Daylight Saving Time!!!

     

    I would prefer to do this within my SQL so no date manipulation needs to be performed within the report. However, I have had no luck doing this or rather don’t know how to & I’m not even sure I will have permissions to store any functions outside of the report itself.

    Within my report, I setup the below function to handle it, but it generates an error when I try to test for Null or 0 values. Otherwise if works just fine. #Error “
    The Value expression for the textbox ‘textbox43’ contains an error: Operator '=' is not defined for type 'Date' and type 'Integer'.”.

     

    Public Function UTCtoLocal(ByVal UTCValue) As Date

     

        ' Get the local time zone and a base Coordinated Universal

        ' Time (UTC).

        Dim localZone As TimeZone = TimeZone.CurrentTimeZone

     

    '    If IsNothing(UTCValue) OR UTCValue = 0 Then

    '        Return Nothing

    '    Else

                ' Calculate the local time and UTC offset.

                Dim localTime As DateTime = _

                    localZone.ToLocalTime(UTCValue)

                Dim localOffset As TimeSpan = _

                    localZone.GetUtcOffset( localTime )

     

            Return localTime

    '    End If

    End Function

     

    I know how to convert from Epoch to UTC via DateAdd, but again it does not handle daylight savings time.

     

    Any ideas or suggestions?

     

    Thanks, Jeff

    Tuesday, February 24, 2009 10:04 PM

Answers

  • Hi Daley2993,

     

     The error is caused by the condition “IsNothing(UTCValue) OR UTCValue = 0” in the code. “UTCValue=0” means the parameter UTCValue is defined as an Integer, and “localZone.ToLocalTime(UTCValue)” means the parameter UTCValue is defined as a Date. A parameter cannot be defined as 2 types at the same time.

     

    To solve the issue, we could change the condition to “IsNothing(UTCValue) Or (Not IsDate(UTCValue))”. Also, please define the function as Shared, or you will get an error “Reference to a non-shared member requires an object reference.”

     

    Here is the sample code:

     

        Public Shared Function UTCtoLocal(ByVal UTCValue) As String

     

            ' Get the local time zone and a base Coordinated Universal

     

            ' Time (UTC).

     

            Dim localZone As TimeZone = TimeZone.CurrentTimeZone

     

            If IsNothing(UTCValue) Or (Not IsDate(UTCValue)) Then

     

                Return Nothing

     

            Else

     

                ' Calculate the local time and UTC offset.

     

                Dim localTime As DateTime = localZone.ToLocalTime(UTCValue)

     

                Dim localOffset As TimeSpan = localZone.GetUtcOffset(localTime)

     

     

                Return localTime.ToString()

     

            End If

     

    End Function

     

     

    Here is a thread for your reference, if you want to convert UTC to local time and handle daylight saving time in T-SQL:

    http://stackoverflow.com/questions/24797/effectively-converting-dates-between-utc-and-local-ie-pst-time-in-sql-2005#25073

     

    If you have any more questions, please feel free to ask.

     

    Thanks,

    Jin


    Jin Chen - MSFT
    • Marked as answer by Jeff Daley Tuesday, March 03, 2009 5:25 PM
    Friday, February 27, 2009 7:19 AM
    Moderator

All replies

  • Hi Daley2993,

     

     The error is caused by the condition “IsNothing(UTCValue) OR UTCValue = 0” in the code. “UTCValue=0” means the parameter UTCValue is defined as an Integer, and “localZone.ToLocalTime(UTCValue)” means the parameter UTCValue is defined as a Date. A parameter cannot be defined as 2 types at the same time.

     

    To solve the issue, we could change the condition to “IsNothing(UTCValue) Or (Not IsDate(UTCValue))”. Also, please define the function as Shared, or you will get an error “Reference to a non-shared member requires an object reference.”

     

    Here is the sample code:

     

        Public Shared Function UTCtoLocal(ByVal UTCValue) As String

     

            ' Get the local time zone and a base Coordinated Universal

     

            ' Time (UTC).

     

            Dim localZone As TimeZone = TimeZone.CurrentTimeZone

     

            If IsNothing(UTCValue) Or (Not IsDate(UTCValue)) Then

     

                Return Nothing

     

            Else

     

                ' Calculate the local time and UTC offset.

     

                Dim localTime As DateTime = localZone.ToLocalTime(UTCValue)

     

                Dim localOffset As TimeSpan = localZone.GetUtcOffset(localTime)

     

     

                Return localTime.ToString()

     

            End If

     

    End Function

     

     

    Here is a thread for your reference, if you want to convert UTC to local time and handle daylight saving time in T-SQL:

    http://stackoverflow.com/questions/24797/effectively-converting-dates-between-utc-and-local-ie-pst-time-in-sql-2005#25073

     

    If you have any more questions, please feel free to ask.

     

    Thanks,

    Jin


    Jin Chen - MSFT
    • Marked as answer by Jeff Daley Tuesday, March 03, 2009 5:25 PM
    Friday, February 27, 2009 7:19 AM
    Moderator
  • Thanks a bunch Jin!

    That worked fine & returning result as a string had no affect on the date formatting. Great.

    I checked out the T-SQL link you provided. It's a viable workaround & I will make a note of it.

    However, I've decided it's probably best to leave the date as UTC in SQL & convert it within the reporting environment via this function.

    Is there a means of making a function, such as this one, available to multiple reports, projects & dbs? Currently storing function within each report under report properties/code . Should I post this question?

    Thanks again, Jeff

    Tuesday, March 03, 2009 6:20 PM
  •  

    Hi Daley2993,

    Sure, we could add the functions for reuse in any report. But the functions must be your own .NET assembly, instead of limiting the scope of your custom functions to a single report under report properties/code.

    We can create a reusable function with custom class library using the following steps:

    1.Create a class library

    a)     In Microsoft Visual Studio, on the File menu, point to New, and then click Project. Click the Visual Basic Projects folder, and then click Class Library. Change the project name to UTCtoLocal.VB.Extensions and specify its location.

    b)     Click OK.

    c)     In Solution Explorer, right-click Class1.vb, click Rename, and then type CommonFunctions.vb.

    Add the following code to the class definition (before the End Class statement):
    Public Shared Function UTCtoLocal(ByVal UTCValue) As String

     

            ' Get the local time zone and a base Coordinated Universal

     

            ' Time (UTC).

     

            Dim localZone As TimeZone = TimeZone.CurrentTimeZone

     

            If IsNothing(UTCValue) Or (Not IsDate(UTCValue)) Then

     

                Return Nothing

     

            Else

     

                ' Calculate the local time and UTC offset.

     

                Dim localTime As DateTime = localZone.ToLocalTime(UTCValue)

     

                Dim localOffset As TimeSpan = localZone.GetUtcOffset(localTime)

     

     

                Return localTime.ToString()

     

            End If

     

    End Function

     

    d)     Save the file.

    e)     Right-click the UTCtoLocal.VB.Extensions solution in Solution Explorer, and then click Build Solution.
    By building the solution, the UTCtoLocal.VB.Extensions.dll file is created in the Folder “bin” under the project folder. You will reference this .dll file in reports in which you want to use this custom function, but first, you need to deploy the file so that Report Server and Report Designer can access the custom function.

    f)      Copy the UTCtoLocal.VB.Extensions.dll assembly to the following locations:
    Report Server C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin
    Report Designer C:\Program Files\Microsoft Visual Studio 8\Common7\IDE \PrivateAssemblies

     

    2. Call a function from a custom assembly

    a)     In Visual Studio, click the Layout tab, and then click Report in the Properties items drop-down list at the top of the Properties window.

    b)     Click the References property, and then click the ellipsis button in the property box.

    c)     Click the ellipsis button in the Edit References dialog box, and then click the Browse tab in the Add Reference dialog box.

    d)     Navigate to the C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies folder, select the UTCtoLocal.VB.Extensions.dll file, and then click Add (or double-click the file name).

    e)     Close all dialog boxes by clicking OK in each. Now that the reference to the assembly is associated with the report, you are ready to call a function in the assembly using an expression in your report.

    f)      Save the report.

    g)     Replace the expression in the textbox with date time with the following code:

    =UTCtoLocal.VB.Extensions.CommonFunctions.UTCtoLocal (UTCDateTimeValue)

     

    If you have any more questions, please feel free to ask.

     

    Thanks,

    Jin

    Wednesday, March 04, 2009 1:40 AM
    Moderator
  • Hi, Jin,

    I do not have a VB Projects folder. I'm running SSMS/BIDS 2005. Does that make sense?

    Thanks, Jeff

    Thursday, March 05, 2009 9:58 PM