Why isn’t there a

SYSUTCDATETIMEOFFSET() 

function for

TODATETIMEOFFSET(SYSUTCDATETIME(),'+00:00') 

After a cursory search, could not find any built-in for SQL Server that do this or anything on the net and had to knock something out very quick. Would be very interested in a more efficient manner to convert to DateTimeOffset in a way that is daylight savings time aware.  This bit of code gives me 3 years to find a better solution.  Of course would be best to just have all the servers set to UTC the problem solved! 🙂

/*************************************************************************
 * NAME:
 * dbo.toUSTZ
 *
 * TYPE:
 * Function
 *
 * DESCRIPTION:
 * Converts a US Timezone to DateTimeOffset accounting for Daylight Savings Time
 * DST in US begins at 2:00 a.m. on the second Sunday of March and
 * ends at 2:00 a.m. on the first Sunday of November
 * REF: http://www.nist.gov/pml/div688/dst.cfm
 *
 * RETURNS:
 * datetimeoffset(0) (ie: '2014-10-31 18:25:51 +00:00')
 *
 * PARAMETERS:
 * @thedate datetime2 --the date to be converted
 * @timezone char(2)  --date's US time zone
 * Code ST  DST Time Zone
 * UT   +0  +0  Universal Time
 * AT   -4  -4  Atlantic
 * ET   -5  -4  Eastern
 * CT   -6  -5  Central
 * MT   -7  -6  Mountain
 * AZ   -7  -7  Arizona
 * PT   -8  -7  Pacific
 * AK   -9  -8  Alaska
 * HT   -10 -10 Hawaii
 * ST   -11 -11 Samoa
 * CH   +10 +10 Chamorro
 *
 * TODO: Add additional years and/or find more elegant way to do this...
 * REF: http://www.nist.gov/pml/div688/localtime.cfm#zones
 *
 * REVISION HISTORY:
 * 20141102 JMO Created
 *************************************************************************/
ALTER FUNCTION [dbo].[toUSTZ]
( @thedate datetime2
, @timezone char(2)
)
RETURNS datetimeoffset(0)
AS
BEGIN
   DECLARE @offsettime datetimeoffset(0)
         , @tzoffset varchar(10)

   --TODO: need function for second sunday in march and first sunday in november
   --      or make lookup table - but would it be more efficient?
   IF    @thedate BETWEEN '20100314 02:00:00' AND '20101107 01:59:59'
      OR @thedate BETWEEN '20110313 02:00:00' AND '20111106 01:59:59'
      OR @thedate BETWEEN '20120311 02:00:00' AND '20121104 01:59:59'
      OR @thedate BETWEEN '20130310 02:00:00' AND '20131103 01:59:59'
      OR @thedate BETWEEN '20140309 02:00:00' AND '20141102 01:59:59'
      OR @thedate BETWEEN '20150308 02:00:00' AND '20151101 01:59:59'
      OR @thedate BETWEEN '20160313 02:00:00' AND '20161106 01:59:59'
      OR @thedate BETWEEN '20170312 02:00:00' AND '20171105 01:59:59'
      OR @thedate BETWEEN '20180311 02:00:00' AND '20181104 01:59:59'
    BEGIN
      SELECT @tzoffset = CASE @timezone
                           WHEN 'UT' THEN '+00:00'
                           WHEN 'AT' THEN '-04:00'
                           WHEN 'ET' THEN '-04:00'
                           WHEN 'CT' THEN '-05:00'
                           WHEN 'MT' THEN '-06:00'
                           WHEN 'AZ' THEN '-07:00'
                           WHEN 'PT' THEN '-07:00'
                           WHEN 'AK' THEN '-08:00'
                           WHEN 'HT' THEN '-10:00'
                           WHEN 'ST' THEN '-11:00'
                           WHEN 'CH' THEN '+10:00'
                           ELSE NULL
                         END
    END
   ELSE
    BEGIN
      SELECT @tzoffset = CASE @timezone 
                           WHEN 'UT' THEN '+00:00'
                           WHEN 'AT' THEN '-04:00'
                           WHEN 'ET' THEN '-05:00'
                           WHEN 'CT' THEN '-06:00'
                           WHEN 'MT' THEN '-07:00'
                           WHEN 'AZ' THEN '-07:00'
                           WHEN 'PT' THEN '-08:00'
                           WHEN 'AK' THEN '-09:00'
                           WHEN 'HT' THEN '-10:00'
                           WHEN 'ST' THEN '-11:00'
                           WHEN 'CH' THEN '+10:00'
                           ELSE NULL
                         END
    END

   SET @offsettime = todatetimeoffset( @thedate, @tzoffset )
   RETURN(@offsettime)
END --f_toUSTZ
GO


/*************************************************************************
 * NAME:
 * dbo.toUTC
 *
 * TYPE:
 * Function
 *
 * DESCRIPTION:
 * Converts to UTC DateTimeOffset accounting for Daylight Savings Time
 *
 * RETURNS:
 * datetimeoffset(0) (ie: '2014-10-31 18:25:51 +00:00')
 *
 * PARAMETERS:
 * @thedate datetime2 --the date to be converted
 * @timezone char(2)  --the date's US time zone
 * Zone ST  DST
 * UT   +0  +0  Universal Time
 * AT   -4  -4  Atlantic
 * ET   -5  -4  Eastern
 * CT   -6  -5  Central
 * MT   -7  -6  Mountain
 * AZ   -7  -7  Arizona
 * PT   -8  -7  Pacific
 * AK   -9  -8  Alaska
 * HT   -10 -10 Hawaii
 * ST   -11 -11 Samoa
 * CH   +10 +10 Chamorro
 * REF: http://www.nist.gov/pml/div688/localtime.cfm#zones
 *
 * REFERENCES: 
 * dbo.toUSTZ
 *
 * REVISION HISTORY:
 * 20141102 JMO Created
 *************************************************************************/
CREATE FUNCTION [dbo].[toUTC]
( @thedate datetime2
, @timezone char(2)
)
RETURNS datetimeoffset(0)
AS
BEGIN
   DECLARE @offsettime datetimeoffset(0)
         , @utctime datetimeoffset(0)

   SET @offsettime = dbo.toUSTZ( @thedate, @timezone )
   SET @utctime = switchoffset( @offsettime, '+00:00' )
   RETURN(@utctime)
END --toUTC

GO

Some monitoring tools turn this on.


http://logicalread.solarwinds.com/why-dpa-uses-sql-server-trace-flag-2861-and-zero-cost-plans-tl01/#.VF5CMvnF9Ps

From Microsoft Technet:

SQL 8 – Cache query plans for queries that have a cost of zero or near to zero.

When turned on, fn_get_sql function can return the SQL text for activities that have zero cost plans kb 325607 

Tip: Avoid Using Trace Flag 2861 to Cache Zero-Cost Query Plans

http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_042705.mspx

Basically if you have a very very busy server, you should turn this off.  Otherwise, if it not making an appreciable difference, it can benefit some monitoring tools.

• Minimize the number of SQL Server logins.
• Use Windows group logins to simplify ongoing management where possible.
• Disable logins rather than dropping them if there is any chance that they will be needed again.
• Ensure that expiry dates are applied to logins that are created for temporary purposes.
• Use fixed server-level roles to delegate server-level management responsibility, and only create user-defined server-level roles if your specific administrative delegation solution requires them.
• Disable the guest user in user databases unless you specifically require guest access.
• Aim to grant the minimum number of explicit permissions possible to meet the security requirements, and use membership of roles and inheritance to ensure the correct effective permissions.
• Ensure every user has only the permission they actually require.

restore headeronly

from disk = ‘V:backupsmydb.bak’

go
restore filelistonlyfrom disk = ‘V:backupsmydb.bak’

go
restore verifyonly

from disk = ‘V:backupsmydb.bak’

go

Bulk-Logged Recovery