none
Converting UTC time to Local time in SQL Server 2008 R2

    Question

  • Background:

    We want to migrate data from oracle database to MS SQL database. We have certain tables with date time column (time saved as UTC). We are accessing this data through views. Currently, in oracle views, we are using oracle specific functions to convert UTC time to local time. We’ve used the function based index approach to improve performance of the queries, hitting those views.

     

    Requirement:

    We need Oracle’s function based index equivalent in MS SQL database or other approaches to handle this situation.

     

    Analysis:

    We can achieve function based index feature in MS SQL with two ways:

    1. Computed Column

    2. Indexed views

     

    Issue:

    We cannot consider computed column approach because of the fact that it becomes database specific and our product should support both Oracle and SQL Server.

    We can only consider indexed view approach. But Indexed views work only with deterministic functions. We have used user-defined function (UDF) to convert UTC time to local time in SQL Server. But the issue is that this UDF is not deterministic.

     

    Question:

    Can we get a deterministic function or alternate recommendation to convert UTC time to local time and vice versa in MS SQL database which when used should provide good performance (Oracle function is provided below for reference).

     

     

    Details:

    In the view definition the function displayLocalTime is used as follows in Oracle. The function returns the local time taking the UTC time as the input.

     

    displayLocalTime(STARTTIME_UTC)

     

    displayLocalTime function

     

    create or replace function displayLocalTime(utc_date in date)

    return date

    DETERMINISTIC

    AS

    local_date date;

    BEGIN

    local_date:=cast(from_tz(cast(utc_date as timestamp), 'UTC') at time zone ‘America/Los_Angeles’ as date);

    return local_date;

    END;

     

    Monday, January 23, 2012 10:29 AM

Answers

  • Below is an example of a C# SQL CLR function that will convert the specified UTC time to local time.  You could mark it deterministic so that you can create an index on the computed column or view but do so at your own risk for the reasons Erland mentioned.

     

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    public partial class UserDefinedFunctions
    {
        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=false)]
    
        public static SqlDateTime displayLocalTime(SqlDateTime dt)
        {
            if (dt.IsNull)
                return dt;
            return TimeZone.CurrentTimeZone.ToLocalTime(dt.Value);
        }
    }
    

    T-SQL deployment code for the above function (.NET 3.5 for SQL 2008):

     

    CREATE ASSEMBLY [SqlClrDateTime]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103009BE8274F0000000000000000E00002210B010800000800000006000000000000EE2700000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000009C2700004F00000000400000F003000000000000000000000000000000000000006000000C000000FC2600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000F4070000002000000008000000020000000000000000000000000000200000602E72737263000000F00300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000D027000000000000480000000200050094200000680600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002002E00000001000011000F00280F00000A16FE010B072D04020A2B19281000000A0F00281100000A6F1200000A281300000A0A2B00062A1E02281400000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000FC010000237E000068020000E802000023537472696E67730000000050050000080000002355530058050000100000002347554944000000680500000001000023426C6F620000000000000002000001471502000900000000FA2533001600000100000013000000020000000200000001000000140000000C00000001000000010000000200000000000A00010000000000060042003B000A006A0055000600A20090000600B90090000600D60090000600F500900006000E01900006002701900006004201900006005D0190000600950176010600A90190000600D501C2013700E901000006001802F80106003802F8010A00800265020600A0023B000600BD023B000000000001000000000001000100010010001D000000050001000100502000000000960076000A0001008A20000000008618870011000200000001008D00190087001500210087001500290087001500310087001500390087001500410087001500490087001500510087001500590087001A00610087001500690087001F007900870025008100870011008900870011001100950242009100A90246001100C6024B009100D00250001100DC025700090087001100200073002A002E002B0064002E0013007B002E001B007B002E00230081002E000B0064002E0033009E002E003B007B002E004B007B002E005B00CD002E006300D6002E006B00DF005E0004800000010000003D119D3200000000000056020000020000000000000000000000010032000000000002000000000000000000000001004900000000000000003C4D6F64756C653E0053716C436C724461746554696D652E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C4461746554696D6500646973706C61794C6F63616C54696D65002E63746F720064740053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C436C724461746554696D65004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F49734E756C6C0054696D655A6F6E65006765745F43757272656E7454696D655A6F6E65004461746554696D65006765745F56616C756500546F4C6F63616C54696D65006F705F496D706C69636974000003200000000000B48C429A0B5B8F4E9A18261CF203AD220008B77A5C561934E0890600011109110903200001042001010E04200101020520010111390420010108170100010054020F497344657465726D696E697374696300032000020400001249042000114D062001114D114D0600011109114D0507021109021601001153716C53657276657250726F6A6563743100000501000000001C0100174865776C6574742D5061636B61726420436F6D70616E7900002E010029436F7079726967687420C2A9204865776C6574742D5061636B61726420436F6D70616E79203230313200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000009BE8274F000000000200000082000000182700001809000052534453D2E09FB86FAA944185DB0B29848D63AD03000000433A5C55736572735C6467757A6D616E2E47555A4D414E315C446F63756D656E74735C56697375616C2053747564696F5C4461746162617365315C53716C53657276657250726F6A656374315C6F626A5C44656275675C53716C436C724461746554696D652E706462000000C42700000000000000000000DE270000002000000000000000000000000000000000000000000000D0270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000980300000000000000000000980334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001009D323D11000001009D323D113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004F8020000010053007400720069006E006700460069006C00650049006E0066006F000000D4020000010030003000300030003000340062003000000050001800010043006F006D00700061006E0079004E0061006D006500000000004800650077006C006500740074002D005000610063006B00610072006400200043006F006D00700061006E00790000004C0012000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C00530065007200760065007200500072006F006A006500630074003100000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003400310033002E00310032003900350037000000000048001300010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C0072004400610074006500540069006D0065002E0064006C006C00000000007800290001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004800650077006C006500740074002D005000610063006B00610072006400200043006F006D00700061006E00790020003200300031003200000000005000130001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C0072004400610074006500540069006D0065002E0064006C006C0000000000440012000100500072006F0064007500630074004E0061006D00650000000000530071006C00530065007200760065007200500072006F006A006500630074003100000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003400310033002E00310032003900350037000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003400310033002E00310032003900350037000000000000000000000000000000000000000000002000000C000000F03700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    WITH PERMISSION_SET = SAFE;

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Edited by Dan GuzmanMVP Tuesday, January 31, 2012 1:15 PM added NULL handling
    • Marked as answer by Naomi NModerator Wednesday, November 14, 2012 7:19 PM
    Sunday, January 29, 2012 11:19 PM

All replies

  • check this good link, http://blogs.msdn.com/bartd/archive/2009/03/31/the-death-of-datetime.aspx

    this will give you a start up.

    Monday, January 23, 2012 11:04 AM
  • One simple way of converting UTC to local is

     

     

    SELECT DATEADD(minute, DATEDIFF(minute,getutcdate(),getdate()), STARTTIME_UTC)
    

    Assuming we can determine 'local' from the machine running the query.  This also avoids having a scalar user-defined function

     

    This is still non-deterministic, but I wouldn't call this from the indexed view, I'd call it from the outer query
    • Edited by Kev Riley Monday, January 23, 2012 11:38 AM
    • Proposed as answer by Skratowicz Wednesday, March 12, 2014 5:49 PM
    • Unproposed as answer by Skratowicz Wednesday, March 12, 2014 5:49 PM
    Monday, January 23, 2012 11:05 AM
  • Hello,

    check out the links below. It mayhelp you out.

    http://stackoverflow.com/questions/6064674/sql-server-2008-how-to-convert-gmtutc-datetime-to-local-datetime

    http://sqlserverperformance.wordpress.com/2007/04/25/one-way-to-convert-from-utc-time-to-local-time/

    Monday, January 23, 2012 11:14 AM
  • I did not check the links that people posted, but my gut feeling is that you cannot do this in an indexed view if you are to follow a spirit. To wit, conversion to local timezone is not determistic. What is the server is moved? What if the local DST rules changes?

    But obviously, you can implement a function that converts the UTC value to a fixed timezone with fixed DST rules that is deterministic. Whether you can do this with dateadd & co I am not sure - SQL Server sometimes has a different idea of what is deterministic than I have. But you can always implement a CLR function and say that is it deterministic. (And if it is not, SQL Server will still believe you, and you will have to sort out the mess yourself.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, January 23, 2012 1:34 PM
  • Thanks for the reply, Kev Riley.

    This solution - using dateadd and datediff - works only for the current time. Since we are doing a migration, we need to handle the historical date values as well. So, it won't work in my case.

    Indexed view is considered just as an option and we may not head in that direction, considering the overhead and maintenance involved.

    Tuesday, January 24, 2012 3:29 AM
  • Thanks for the reply, danilribiero.

    The links provide the same solution, which Kev Riley has suggested. Please refer my reply to his post.

    Tuesday, January 24, 2012 3:33 AM
  • Thanks for your reply, jimmweaver.

    Since we need to migrate and work with historical data and the design should be similar for both Oracle and SQL Server(we use a single set of hibernate(hbm) files), I am not sure, if we can use datetimeoffset. will check the feasibility.

    Tuesday, January 24, 2012 4:11 AM
  • Thanks for the reply, Erland Sommarskog.

    We have implemented a function, which does the conversion from local to UTC and UTC to local times. It is not robust, and does not cover all the DST conversions.

    We haven't tried the CLR approach, using the .NET libraries. I haven't written any CLR function, till date, so can you please let me know the precautions I might have to take to write and deploy a CLR function.

    P.S.: The application is written in Java and we are not using .NET.

    Tuesday, January 24, 2012 4:25 AM
  • We have implemented a function, which does the conversion from local to UTC and UTC to local times. It is not robust, and does not cover all the DST conversions.

    If that is required for you, you will need to use the CLR. To do this reliably in SQL Server would need to have tables with CLR function.

    We haven't tried the CLR approach, using the .NET libraries. I haven't written any CLR function, till date, so can you please let me know the precautions I might have to take to write and deploy a CLR function.

    I can only recommend reading Books Online, and other sources you find one the web. A scalar user-defined function in the CLR is fairly straightforward. Note that you don't need Visual Studio, but you can use the .Net Framework SDK which is a free download. The tricky part is to deploy the assembly on the server, since you need to bring the DLL to the server itself, unless you write something which reads the DLL makes a hexstring of it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, January 24, 2012 8:25 AM
  • PrasannaKK,

    the solution only uses the current date/time to determine the offset between local and UTC.  It then applies that to the field STARTTIME_UTC, so you can use this against historical values.

    Kev

    Tuesday, January 24, 2012 8:36 AM
  • The below scenario will help me explain the point:

    Suppose you have the local datetime value 2008-03-05 08:30:00 in the database, and you need to convert this to local time(Pacific time zone). Determine the current time zone offset for the local server:

                    DATEDIFF (minute, GETUTCDATE(), GETDATE())

    This tells you that the local server time is 7 hours behind UTC right now, so you should be able to subtract 7 hours from the utc time to get the equivalent local time.  That would be wrong; Daylight Savings Time is in effect in the U.S. today (April), but when this datetime value was collected back in March, DST was not in effect.  The correct time zone offset to use is UTC minus 8 hours.


    Prasanna Kumar K
    Tuesday, January 24, 2012 9:03 AM
  • As you have seen from the article that you got that example from, if you want to arbitrarily convert historical datetimes to local time zones, you should be storing the data as datetimeoffset data types, and possibly only deal with UTC.

     

    That probably doesn't help your situation right now, but after reading that article, it does make me wonder how correct the Oracle function is too?

    Tuesday, January 24, 2012 9:49 AM
  • but after reading that article, it does make me wonder how correct the Oracle function is too?

    The Oracle function uses the Java timezone libraries to handle DST conversions. So, it covers almost all the scenarios(I might be wrong here ;) ).

    Prasanna Kumar K
    Tuesday, January 24, 2012 10:14 AM
  • Sunday, January 29, 2012 10:40 PM
    Moderator
  • Below is an example of a C# SQL CLR function that will convert the specified UTC time to local time.  You could mark it deterministic so that you can create an index on the computed column or view but do so at your own risk for the reasons Erland mentioned.

     

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    public partial class UserDefinedFunctions
    {
        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=false)]
    
        public static SqlDateTime displayLocalTime(SqlDateTime dt)
        {
            if (dt.IsNull)
                return dt;
            return TimeZone.CurrentTimeZone.ToLocalTime(dt.Value);
        }
    }
    

    T-SQL deployment code for the above function (.NET 3.5 for SQL 2008):

     

    CREATE ASSEMBLY [SqlClrDateTime]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103009BE8274F0000000000000000E00002210B010800000800000006000000000000EE2700000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000009C2700004F00000000400000F003000000000000000000000000000000000000006000000C000000FC2600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000F4070000002000000008000000020000000000000000000000000000200000602E72737263000000F00300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000D027000000000000480000000200050094200000680600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002002E00000001000011000F00280F00000A16FE010B072D04020A2B19281000000A0F00281100000A6F1200000A281300000A0A2B00062A1E02281400000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000FC010000237E000068020000E802000023537472696E67730000000050050000080000002355530058050000100000002347554944000000680500000001000023426C6F620000000000000002000001471502000900000000FA2533001600000100000013000000020000000200000001000000140000000C00000001000000010000000200000000000A00010000000000060042003B000A006A0055000600A20090000600B90090000600D60090000600F500900006000E01900006002701900006004201900006005D0190000600950176010600A90190000600D501C2013700E901000006001802F80106003802F8010A00800265020600A0023B000600BD023B000000000001000000000001000100010010001D000000050001000100502000000000960076000A0001008A20000000008618870011000200000001008D00190087001500210087001500290087001500310087001500390087001500410087001500490087001500510087001500590087001A00610087001500690087001F007900870025008100870011008900870011001100950242009100A90246001100C6024B009100D00250001100DC025700090087001100200073002A002E002B0064002E0013007B002E001B007B002E00230081002E000B0064002E0033009E002E003B007B002E004B007B002E005B00CD002E006300D6002E006B00DF005E0004800000010000003D119D3200000000000056020000020000000000000000000000010032000000000002000000000000000000000001004900000000000000003C4D6F64756C653E0053716C436C724461746554696D652E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C4461746554696D6500646973706C61794C6F63616C54696D65002E63746F720064740053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C436C724461746554696D65004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F49734E756C6C0054696D655A6F6E65006765745F43757272656E7454696D655A6F6E65004461746554696D65006765745F56616C756500546F4C6F63616C54696D65006F705F496D706C69636974000003200000000000B48C429A0B5B8F4E9A18261CF203AD220008B77A5C561934E0890600011109110903200001042001010E04200101020520010111390420010108170100010054020F497344657465726D696E697374696300032000020400001249042000114D062001114D114D0600011109114D0507021109021601001153716C53657276657250726F6A6563743100000501000000001C0100174865776C6574742D5061636B61726420436F6D70616E7900002E010029436F7079726967687420C2A9204865776C6574742D5061636B61726420436F6D70616E79203230313200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000009BE8274F000000000200000082000000182700001809000052534453D2E09FB86FAA944185DB0B29848D63AD03000000433A5C55736572735C6467757A6D616E2E47555A4D414E315C446F63756D656E74735C56697375616C2053747564696F5C4461746162617365315C53716C53657276657250726F6A656374315C6F626A5C44656275675C53716C436C724461746554696D652E706462000000C42700000000000000000000DE270000002000000000000000000000000000000000000000000000D0270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000980300000000000000000000980334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001009D323D11000001009D323D113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004F8020000010053007400720069006E006700460069006C00650049006E0066006F000000D4020000010030003000300030003000340062003000000050001800010043006F006D00700061006E0079004E0061006D006500000000004800650077006C006500740074002D005000610063006B00610072006400200043006F006D00700061006E00790000004C0012000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C00530065007200760065007200500072006F006A006500630074003100000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003400310033002E00310032003900350037000000000048001300010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C0072004400610074006500540069006D0065002E0064006C006C00000000007800290001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004800650077006C006500740074002D005000610063006B00610072006400200043006F006D00700061006E00790020003200300031003200000000005000130001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C0072004400610074006500540069006D0065002E0064006C006C0000000000440012000100500072006F0064007500630074004E0061006D00650000000000530071006C00530065007200760065007200500072006F006A006500630074003100000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003400310033002E00310032003900350037000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003400310033002E00310032003900350037000000000000000000000000000000000000000000002000000C000000F03700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    WITH PERMISSION_SET = SAFE;

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Edited by Dan GuzmanMVP Tuesday, January 31, 2012 1:15 PM added NULL handling
    • Marked as answer by Naomi NModerator Wednesday, November 14, 2012 7:19 PM
    Sunday, January 29, 2012 11:19 PM
  • We have changed the design to convert the user input before quering against the database and there by eliminating the need for Function based indexes.

    We are working to find a robust solution for Date conversions between UTC and local time.


    Prasanna Kumar K
    Monday, February 06, 2012 4:38 AM
  • We have changed the design to convert the user input before quering against the database and there by eliminating the need for Function based indexes.

    We are working to find a robust solution for Date conversions between UTC and local time.


    The front-end approach will work as long as your UI allows the user to specify the desired timezone for ambiguous values.  For example, with a client in the Eastern US, the value '2011-11-06 01:30:00' could be interpreted as either Eastern Daylight Time or Eastern Standard Time.  The user would need a method to differentiate between the two.

    If by "robust" you mean in T-SQL, the SQLCLR solution is probably the easiest method and can be extended to fit your needs.  It is also possible to create a calendar table along with other objects to support such conversions but you should consider that timezones can and do change over time so you will need to stay on top of legislative changes that affect such conversions.  With the CLR (or Java) run time, patches take care of those changes.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, February 06, 2012 1:27 PM
  • Perfect!
    Thursday, November 08, 2012 9:39 PM
  • To finalize Guzman comment, you must do 

    CREATE ASSEMBLY UTCtoLocalTime from 'c:\UTCtoLocalTime.dll'

    and you create this function which will be persisted if you put  IsDeterministic=true

    CREATE FUNCTION UTCtoLocalDate (@UTCDate DATETIME)

    RETURNS DATETIME
    AS EXTERNAL NAME UTCToLocalTime.[UserDefinedFunctions].displayLocalTime 
    GO

    Wednesday, November 14, 2012 7:12 PM