Hi,

    We are storing some date stamp values for our application in GMT. Based
on the user logged in, we need to convert these values to the local time of
the time zone specified by the user. Is there any direct function or user
defined function avialable for this

Thanks and Regards,
Santosh

I think it can be handled at the front end

regards Lara


Show quoteHide quote

Yes it can be. We have developed the compnent for the same. But the problem
with that approach is that whereever we have to display the data we will have
to call this function. It means that in the report we will have to loop
through all the records, convert the GMT to local time and then send it to
report writer.

Santosh

Show quoteHide quote

Hi,
If u get the time zone from the front end, pass that value to the sp( for
report generation); at the Sp level u can use this value to calculate the
exact timezone. like (DateTimeCol + @zoneDiff)  AS DateTimeCol.  Is that OK?
Othewise i dont think, it is possible.

regards Lara



Show quoteHide quote

Its not that simple. The calculation of the local time does not just involve
adding of the bias. The complex part of it is the daylight saving
caluculation.

Santosh

Show quoteHide quote

The problem is the server does not know the time zone that the client is on.

So at the very least the client will have to pass this to the server, at
this point the client already has a function to look up the time zone
information so why not convert the time too.

But I do agree with you that a function to return the time in local format
would be great.

However there is another problem too... the client machine may not be
automatically set up to adjust for Day Light Savings ... so this would need
to be taken into account at the server too.

Show quoteHide quote

I worked out a solution for you (I hope).  If you want to poke at it right
away, the code is below.  I will post the VS project (containing all udfs,
sproc, and test.sql) to channel9.  Basically it is one class
TimeZoneInformation.  This class does all the work.  We then create very
thin UDF and sproc Sql Clr wrappers that call into static method of the
class.  Use a VS Sql Server Project and add all this stuff and deploy to
your database.  Then just call the UDFs and sproc from tsql as needed.  It
uses win32 apis to do the timezone conversions to and from UTC and local
time zones so win32 does the grunt work around handling DST (hopefully
correctly).  I tested for function, but not all timezones and not sure what
funky date edge cases may need to be tested. Would appreciate anyone with
some creative date test cases to pound on it.

UDFs
-------
ToLocalTime  (convert a UTC to a local time zone respecting any DST)
ToUniversalTime ( convert a local time in a named time zone to UTC)
ToLocalTimeFromLocalTime ( convert one local time to a time in another time
zone)

Sproc
------
GetStandardTimeZoneNames (list of standard, daylight, and display names used
by windows)

So in your case, the client only needs to send you their standard time zone
name string (get using TimeZone.CurrentTimeZone.StandardName at the client)
or you could have that stored in a table already.  Once you have that, you
can convert any UTC datetime to a client's local time (respecting DST) from
your sprocs, etc.

Here is the code:
-------------------
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Runtime.InteropServices;
using Microsoft.Win32;

namespace SqlUtils
{
    /// <summary>
    /// Time zone conversion helper class.  Used to convert to/from utc
times and local times and respect DST.
    /// Can also covert between two local time zones.
    /// </summary>
    public class TimeZoneInformation
    {
        #region Fields
        private TZI tzi;                                    // Current time
zone information.
        private string displayName;                         // Current time
zone display name.
        private string standardName;                        // Current time
zone standard name (non-DST).
        private string daylightName;                        // Current time
zone daylight name (DST).
        private static readonly List<TimeZoneInformation> timeZones; // List
of all time zones on machine.
        #endregion

        #region Constructors
        private TimeZoneInformation()
        {
        }

        static TimeZoneInformation()
        {
            timeZones = new List<TimeZoneInformation>();

            using (RegistryKey key =
Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Windows
NT\CurrentVersion\Time Zones"))
            {
                string[] zoneNames = key.GetSubKeyNames();

                foreach (string zoneName in zoneNames)
                {
                    using (RegistryKey subKey = key.OpenSubKey(zoneName))
                    {
                        TimeZoneInformation tzi = new TimeZoneInformation();
                        tzi.displayName =
(string)subKey.GetValue("Display");
                        tzi.standardName = (string)subKey.GetValue("Std");
                        tzi.daylightName = (string)subKey.GetValue("Dlt");
                        tzi.InitTzi((byte[])subKey.GetValue("Tzi"));
                        timeZones.Add(tzi);
                    }
                }
            }
        }
        #endregion

        #region Public Properties
        /// <summary>
        /// Gets list of all time zones defined on the current computer
system.
        /// </summary>
        public static List<TimeZoneInformation> TimeZones
        {
            get
            {
                List<TimeZoneInformation> nList = new
List<TimeZoneInformation>();
                foreach (TimeZoneInformation tzi in timeZones)
                {
                    nList.Add(tzi);
                }
                return nList;
            }
        }

        /// <summary>
        /// Gets the time zone information of the current computer system.
        /// </summary>
        public static TimeZoneInformation CurrentTimeZone
        {
            get
            {
                string tzn = TimeZone.CurrentTimeZone.StandardName;
                TimeZoneInformation tzi =
TimeZoneInformation.GetTimeZone(tzn);
                return tzi;
            }
        }

        /// <summary>
        /// The time zone's name during 'standard' time (i.e. not daylight
savings).
        /// </summary>
        public string StandardName
        {
            get
            {
                return standardName;
            }
        }

        /// <summary>
        /// The time zone's name during daylight savings time (DST).
        /// </summary>
        public string DaylightName
        {
            get
            {
                return daylightName;
            }
        }

        /// <summary>
        /// The time zone's display name (e.g. "(GMT-05:00) Eastern Time (US
& Canada)").
        /// </summary>
        public string DisplayName
        {
            get
            {
                return displayName;
            }
        }

        /// <summary>
        /// The offset from UTC. Local = UTC + Bias.
        /// </summary>
        public int Bias
        {
            // Biases in the registry are defined as UTC = local + bias
            // We return as Local = UTC + bias
            get
            {
                return -tzi.bias;
            }
        }

        /// <summary>
        /// The offset from UTC during standard time.
        /// </summary>
        public int StandardBias
        {
            get
            {
                return -(tzi.bias + tzi.standardBias);
            }
        }

        /// <summary>
        /// The offset from UTC during daylight time.
        /// </summary>
        public int DaylightBias
        {
            get
            {
                return -(tzi.bias + tzi.daylightBias);
            }
        }
        #endregion

        #region Public Methods

        /// <summary>
        /// Returns standard name of this time zone instance.
        /// </summary>
        /// <returns>Time zone standard name.</returns>
        public override string ToString()
        {
            return this.standardName;
        }

        /// <summary>
        /// Returns a TimeZoneInformation instance for the time zone with
supplied standard name.
        /// </summary>
        /// <param name="standardTimeZoneName">Standard name of the time
zone.</param>
        /// <returns>TimeZoneInformation instance.</returns>
        /// <exception cref="ArgumentException">Thrown if name not
found.</exception>
        public static TimeZoneInformation GetTimeZone(string
standardTimeZoneName)
        {
            if (standardTimeZoneName == null)
                throw new ArgumentNullException("standardName");

            foreach (TimeZoneInformation tzi in
TimeZoneInformation.TimeZones)
            {
                if (tzi.StandardName.Equals(standardTimeZoneName,
StringComparison.OrdinalIgnoreCase))
                    return tzi;
            }
            throw new ArgumentException("standardTimeZoneName not found.");
        }

        /// <summary>
        /// Converts the value of the utc time to a local time in this time
zone.
        /// </summary>
        /// <param name="utc">The UTC time to convert.</param>
        /// <returns>The local time.</returns>
        public DateTime ToLocalTime(DateTime utc)
        {
            // Convert to SYSTEMTIME
            SYSTEMTIME stUTC = DateTimeToSystemTime(utc);

            // Set up the TIME_ZONE_INFORMATION
            TIME_ZONE_INFORMATION tziNative = TziNative();
            SYSTEMTIME stLocal;
            NativeMethods.SystemTimeToTzSpecificLocalTime(ref tziNative, ref
stUTC, out stLocal);

            // Convert back to DateTime
            return SystemTimeToDateTime(ref stLocal);
        }

        /// <summary>
        /// Converts the value of the utc time to local time in supplied
time zone.
        /// </summary>
        /// <param name="standardTimeZoneName">The standard name of the time
zone.</param>
        /// <param name="utc">The time to convert.</param>
        /// <returns>The local time.</returns>
        /// <exception cref="ArgumentException">Thrown if time zone not
found.</exception>
        public static DateTime ToLocalTime(string standardTimeZoneName,
DateTime utc)
        {
            TimeZoneInformation tzi =
TimeZoneInformation.GetTimeZone(standardTimeZoneName);
            return tzi.ToLocalTime(utc);
        }

        /// <summary>
        /// Converts a localTime from a source time zone to a target time
zone, adjusting for DST as needed.
        /// The localTime must be a local time in the sourceTimeZoneName
time zone.
        /// </summary>
        /// <param name="sourceTimeZoneName">Time zone name which represents
localTime.</param>
        /// <param name="localTime">The source local time.</param>
        /// <param name="targetTimeZoneName">The time zone name which to
convert the localTime.</param>
        /// <returns>The local time for targetTimeZoneName.</returns>
        public static DateTime ToLocalTime(string sourceTimeZoneName,
DateTime localTime, string targetTimeZoneName)
        {
            DateTime utc =
TimeZoneInformation.ToUniversalTime(sourceTimeZoneName, localTime);
            DateTime lt =
TimeZoneInformation.ToLocalTime(targetTimeZoneName, utc);
            return lt;
        }

        /// <summary>
        /// Converts the value of the local time to UTC time.
        /// Note that there may be different possible interpretations at the
daylight time boundaries.
        /// </summary>
        /// <param name="local">The local time to convert.</param>
        /// <returns>The UTC DateTime.</returns>
        /// <exception cref="NotSupportedException">Thrown if the method
failed due to missing platform support.</exception>
        public DateTime ToUniversalTime(DateTime local)
        {
            SYSTEMTIME stLocal = DateTimeToSystemTime(local);
            TIME_ZONE_INFORMATION tziNative = TziNative();
            SYSTEMTIME stUTC;

            try
            {
                NativeMethods.TzSpecificLocalTimeToSystemTime(ref tziNative,
ref stLocal, out stUTC);
                return SystemTimeToDateTime(ref stUTC);
            }
            catch (EntryPointNotFoundException e)
            {
                throw new NotSupportedException("This method is not
supported on this operating system", e);
            }
        }

        /// <summary>
        /// Converts a local time in specified time zone to UTC time.
        /// </summary>
        /// <param name="standardTimeZoneName">The standard time zone
name.</param>
        /// <param name="local">The local time to convert.</param>
        /// <returns>The UTC time.</returns>
        /// <exception cref="ArgumentException">Thrown if time zone name not
found.</exception>
        /// <exception cref="NotSupportedException">Thrown if the method
failed due to missing platform support.</exception>
        public static DateTime ToUniversalTime(string standardTimeZoneName,
DateTime local)
        {
            TimeZoneInformation tzi =
TimeZoneInformation.GetTimeZone(standardTimeZoneName);
            return tzi.ToUniversalTime(local);
        }
        #endregion

        #region Private Methods
        private static SYSTEMTIME DateTimeToSystemTime(DateTime dt)
        {
            SYSTEMTIME st;
            FILETIME ft = new FILETIME();

            ft.dwHighDateTime = (int)(dt.Ticks >> 32);
            ft.dwLowDateTime = (int)(dt.Ticks & 0xFFFFFFFFL);

            NativeMethods.FileTimeToSystemTime(ref ft, out st);

            return st;
        }


        private static DateTime SystemTimeToDateTime(ref SYSTEMTIME st)
        {
            FILETIME ft = new FILETIME();

            NativeMethods.SystemTimeToFileTime(ref st, out ft);

            DateTime dt = new DateTime((((long)ft.dwHighDateTime) << 32) |
(uint)ft.dwLowDateTime);

            return dt;
        }

        private TIME_ZONE_INFORMATION TziNative()
        {
            TIME_ZONE_INFORMATION tziNative = new TIME_ZONE_INFORMATION();

            tziNative.Bias = tzi.bias;
            tziNative.StandardDate = tzi.standardDate;
            tziNative.StandardBias = tzi.standardBias;
            tziNative.DaylightDate = tzi.daylightDate;
            tziNative.DaylightBias = tzi.daylightBias;

            return tziNative;
        }

        /// <summary>
        /// The standard Windows SYSTEMTIME structure.
        /// </summary>
        [StructLayout(LayoutKind.Sequential)]
        private struct SYSTEMTIME
        {
            public UInt16 wYear;
            public UInt16 wMonth;
            public UInt16 wDayOfWeek;
            public UInt16 wDay;
            public UInt16 wHour;
            public UInt16 wMinute;
            public UInt16 wSecond;
            public UInt16 wMilliseconds;
        }

        // FILETIME is already declared in System.Runtime.InteropServices.

        /// <summary>
        /// The layout of the Tzi value in the registry.
        /// </summary>
        [StructLayout(LayoutKind.Sequential)]
        private struct TZI
        {
            public int bias;
            public int standardBias;
            public int daylightBias;
            public SYSTEMTIME standardDate;
            public SYSTEMTIME daylightDate;
        }


        /// <summary>
        /// The standard Win32 TIME_ZONE_INFORMATION structure.
        /// Thanks to www.pinvoke.net.
        /// </summary>
        [StructLayout(LayoutKind.Sequential, CharSet = CharSet.Unicode)]
        private struct TIME_ZONE_INFORMATION
        {
            [MarshalAs(UnmanagedType.I4)]
            public Int32 Bias;
            [MarshalAs(UnmanagedType.ByValTStr, SizeConst = 32)]
            public string StandardName;
            public SYSTEMTIME StandardDate;
            [MarshalAs(UnmanagedType.I4)]
            public Int32 StandardBias;
            [MarshalAs(UnmanagedType.ByValTStr, SizeConst = 32)]
            public string DaylightName;
            public SYSTEMTIME DaylightDate;
            [MarshalAs(UnmanagedType.I4)]
            public Int32 DaylightBias;
        }

        /// <summary>
        /// A container for P/Invoke declarations.
        /// </summary>
        private struct NativeMethods
        {
            private const string KERNEL32 = "kernel32.dll";

            [DllImport(KERNEL32)]
            public static extern uint GetTimeZoneInformation(out
TIME_ZONE_INFORMATION
                lpTimeZoneInformation);

            [DllImport(KERNEL32)]
            public static extern bool SystemTimeToTzSpecificLocalTime(
                [In] ref TIME_ZONE_INFORMATION lpTimeZone,
                [In] ref SYSTEMTIME lpUniversalTime,
                out SYSTEMTIME lpLocalTime);

            [DllImport(KERNEL32)]
            public static extern bool SystemTimeToFileTime(
                [In] ref SYSTEMTIME lpSystemTime,
                out FILETIME lpFileTime);

            [DllImport(KERNEL32)]
            public static extern bool FileTimeToSystemTime(
                [In] ref FILETIME lpFileTime,
                out SYSTEMTIME lpSystemTime);

            /// <summary>
            /// Convert a local time to UTC, using the supplied time zone
information.
            /// Windows XP and Server 2003 and later only.
            /// </summary>
            /// <param name="lpTimeZone">The time zone to use.</param>
            /// <param name="lpLocalTime">The local time to convert.</param>
            /// <param name="lpUniversalTime">The resultant time in
UTC.</param>
            /// <returns>true if successful, false otherwise.</returns>
            [DllImport(KERNEL32)]
            public static extern bool TzSpecificLocalTimeToSystemTime(
                [In] ref TIME_ZONE_INFORMATION lpTimeZone,
                [In] ref SYSTEMTIME lpLocalTime,
                out SYSTEMTIME lpUniversalTime);
        }

        /// <summary>
        /// Initialise the m_tzi member.
        /// </summary>
        /// <param name="info">The Tzi data from the registry.</param>
        private void InitTzi(byte[] info)
        {
            if (info.Length != Marshal.SizeOf(tzi))
            {
                throw new ArgumentException("Information size is incorrect",
"info");
            }

            // Could have sworn there's a Marshal operation to pack bytes
into
            // a structure, but I can't see it. Do it manually.

            GCHandle h = GCHandle.Alloc(info, GCHandleType.Pinned);

            //TimeZone tz;
            //tz.ToLocalTime;
            //tz.ToUniversalTime;

            try
            {
                tzi = (TZI)Marshal.PtrToStructure(h.AddrOfPinnedObject(),
typeof(TZI));
            }
            finally
            {
                h.Free();
            }
        }
        #endregion
    }
}

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlUtils;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetTimeZoneNames()
    {
        // Create a record object that represents an individual row,
including it's metadata.
        SqlDataRecord record = new SqlDataRecord(
            new SqlMetaData("StandardName", SqlDbType.NVarChar, 128),
            new SqlMetaData("DaylightName", SqlDbType.NVarChar, 128),
            new SqlMetaData("DisplayName", SqlDbType.NVarChar, 128));

        SqlContext.Pipe.SendResultsStart(record);

        foreach(TimeZoneInformation tzi in TimeZoneInformation.TimeZones)
        {
            // Populate the record.
            record.SetSqlString(0, tzi.StandardName);
            record.SetSqlString(1, tzi.DaylightName);
            record.SetSqlString(2, tzi.DisplayName);

            // Send the record to the pipe.
            SqlContext.Pipe.SendResultsRow(record);
        }
        SqlContext.Pipe.SendResultsEnd();

    }
};

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlUtils;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime ToLocalTime(string standardTimeZoneName, DateTime
utc)
    {
        return TimeZoneInformation.ToLocalTime(standardTimeZoneName, utc);
    }
};

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlUtils;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime ToLocalTimeFromLocalTime(string
standardTimeZoneName, DateTime localTime, string targetTimeZoneName)
    {
        return TimeZoneInformation.ToLocalTime(standardTimeZoneName,
localTime, targetTimeZoneName);
    }
};

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlUtils;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime ToUniversalTime(string standardTimeZoneName,
DateTime localTime)
    {
        return TimeZoneInformation.ToUniversalTime(standardTimeZoneName,
localTime);
    }
};


-- Test Script
-- Convert two UTC dates to EST time zone. First date is inside DST of EST.
Second is not in DST of EST.
SELECT dbo.ToLocalTime(N'Eastern Standard Time',
CONVERT(DATETIME,'10/29/2005 1pm')) AS LocalTime;
select dbo.ToLocalTime(N'Pacific Standard Time',
CONVERT(DATETIME,'10/29/2005 1pm')) AS LocalTime;
SELECT dbo.ToLocalTime(N'Eastern Standard Time', CONVERT(DATETIME,'11/1/2005
1pm')) AS LocalTime;

-- Convert two EST dates to UTC. First date in inside DST. Second is not in
DST.
select dbo.ToUniversalTime(N'Eastern Standard Time', Convert(datetime,
'10/29/2005 9am')) as LocalTime;
select dbo.ToUniversalTime(N'eastern standard time', convert(datetime,
'11/1/2005 8am')) as LocalTime;

-- Convert a local EST time to PST (i.e. 12/3/2005 10:32pm EST is 12/3/2005
7:32pm PST).
select dbo.ToLocalTimeFromLocalTime(N'Eastern Standard Time',
Convert(DateTime, '12/3/2005 10:32pm'), N'Pacific Standard Time') as
LocalTime

Hi Willam,

  Will this work with Sql Server 2000 and VS.net 2003 ?

Santosh

Show quoteHide quote

No,  clr stored procedures are for 2005 only.

I believe that the best solution for this in SQL2000 will be to write a UDF
using T-SQL.  You'll need to write the function to pass in the country that
you are wanting the report for.  The UDF will have two parameters, the GMT
datetime to convert and the country that you want to localise to.  The idea
is that it checks the GMT date time, checks the date portion to see if
daylight savings time should be applied (depending on the date and the
country) and calculates the correct offset for the GMT date.  This is then
added to the GMT date time and returned as a scalar value.

I believe that it will be more efficient for SQL server to do this work as
opposed to the client, because the server is already parsing the data
anyway.  It will be a relativly minor thing to add this date.  If however
you are parsing the entire record set on the client - say to convert it to
the report you may also want to consider that the code will probably be
easier to accomplish on the client and you won't loose anything if you're
already reading all the dates anyway.


Show quoteHide quote

Hi Colin.  It is the time zone that is important here, not the country.
Most countries have more then one time zone.  So New York is going to be
different then LA.

This will work, but you then need to create and maintain your own time zone
database and keep it updated with changes.  Using my sample, you let windows
handle that.  However, as you said, it will only work with Sql2005 as it has
clr support.  You could create a xsp in c in sql 2000 I guess.  Any takers
on that?

In things like reporting server or server jobs, you almost have to let sql
do it, otherwise you would need to make roundtrips to the client to make
each date conversion.

Agreed.

Sorry, it will only work for Sql2005.  To do they same thing in 2000 would
require writing an extended stored proc in C I believe or creating all the
timezone table data and working all that out in TQL.  I would have to bet
that someone out there has done that before, but have not seen it myself.
Cheers.

Show quoteHide quote

I see you getting the timezone of the server but I may be missing it, where
do you get the time zone of the CLIENT ?

You need to send it to the server using what ever method.  In a report, I
guess it could be a report parm.  In a spoc, you can pass as a parm.  I
guess you could also keep it in a client preferences info table.  Also note
I just posted the source to:
http://channel9.msdn.com/ShowPost.aspx?PostID=142586

You need to fix up the connection of the project to your DB and compile and
deploy.  Let me know if anyone has issues with deploying the project.
Cheers.

Show quoteHide quote

SQL Server index low cardinality columns
running stored procedures from SQL Agent
need output where same column has more than one pirce of data
query help - "Most Helpful Review" idea
Help developing this project.