SQL Server - Convert date field to UTC
I have re开发者_开发问答cently updated my system to record date/times as UTC as previously they were storing as local time.
I now need to convert all the local stored date/times to UTC. I was wondering if there is any built in function, similar to .NET's ConvertTime method?
I am trying to avoid having to write a utility app to do this for me.
Any suggestions?
I do not believe the above code will work. The reason is that it depends upon the difference between the current date in local and UTC times. For example, here in California we are now in PDT (Pacific Daylight Time); the difference between this time and UTC is 7 hours. The code provided will, if run now, add 7 hours to every date which is desired to be converted. But if a historical stored date, or a date in the future, is converted, and that date is not during daylight savings time, it will still add 7, when the correct offset is 8. Bottom line: you cannot convert date/times properly between time zones (including UTC, which does not obey daylight savings time) by only looking at the current date. You must consider the date itself that you are converting, as to whether daylight time was in force on that date. Furthermore, the dates at which daylight and standard times change themselves have changed (George Bush changed the dates during his administration for the USA!). In other words, any solution which even references getdate() or getutcdate() does not work. It must parse the actual date to be converted.
With SQL Server 2016, there is now built-in support for time zones with the AT TIME ZONE statement. You can chain these to do conversions:
SELECT YourOriginalDateTime AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC'
Or, this would work as well:
SELECT SWITCHOFFSET(YourOriginalDateTime AT TIME ZONE 'Pacific Standard Time', '+00:00')
Either of these will interpret the input in Pacific time, properly account for whether or not DST is in effect, and then convert to UTC. The result will be a datetimeoffset with a zero offset.
More examples in the CTP announcement.
If they're all local to you, then here's the offset:
SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime
and you should be able to update all the data using:
UPDATE SomeTable
SET DateTimeStamp = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), DateTimeStamp)
Would that work, or am I missing another angle of this problem?
As mentioned here previously, there is no build-in way to perform time zone rules aware date conversion in SQL Server (at least as of SQL Server 2012).
You have essentially three choices to do this right:
- Perform the conversion outside of SQL Server and store results in the database
- Introduce time zone offset rules in a standalone table and create stored procedures or UDFs to reference the rules table to perform conversions. You can find one take on this approach over at SQL Server Central (registration required)
- You can create a SQL CLR UDF; I will describe the approach here
While SQL Server does not offer tools to perform time zone rules aware date conversion, the .NET framework does, and as long as you can use SQL CLR, you can take advantage of that.
In Visual Studio 2012, make sure you have the data tools installed (otherwise, SQL Server project won't show up as an option), and create a new SQL Server project.
Then, add a new SQL CLR C# User Defined Function, call it "ConvertToUtc". VS will generate boiler plate for you that should look something like this:
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ConvertToUtc()
{
// Put your code here
return new SqlString (string.Empty);
}
}
We want to make several changes here. For one, we want to return a SqlDateTime rather than a SqlString. Secondly, we want to do something useful. :)
Your revised code should look like this:
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime ConvertToUtc(SqlDateTime sqlLocalDate)
{
// convert to UTC and use explicit conversion
// to return a SqlDateTime
return TimeZone.CurrentTimeZone.ToUniversalTime(sqlLocalDate.Value);
}
}
At this point, we are ready to try it out. The simplest way is to use the built-in Publish facility in Visual Studio. Right-click on the database project and select "Publish". Set up your database connection and name, and then either click "Publish" to push the code into the database or click "Generate Script" if you'd like to store the script for posterity (or to push the bits into production).
Once you have the UDF in the database, you can see it in action:
declare @dt as datetime
set @dt = '12/1/2013 1:00 pm'
select dbo.ConvertToUtc(@dt)
Here is a tested procedure that upgraded my database from local to utc time. The only input required to upgrade a database is to enter the number of minutes local time is offset from utc time into @Offset and if the timezone is subject to daylight savings adjustments by setting @ApplyDaylightSavings.
For example, US Central Time would enter @Offset=-360 and @ApplyDaylightSavings=1 for 6 hours and yes apply daylight savings adjustment.
Supporting Database Function
CREATE FUNCTION [dbo].[GetUtcDateTime](@LocalDateTime DATETIME, @Offset smallint, @ApplyDaylightSavings bit)
RETURNS DATETIME AS BEGIN
--====================================================
--Calculate the Offset Datetime
--====================================================
DECLARE @UtcDateTime AS DATETIME
SET @UtcDateTime = DATEADD(MINUTE, @Offset * -1, @LocalDateTime)
IF @ApplyDaylightSavings = 0 RETURN @UtcDateTime;
--====================================================
--Calculate the DST Offset for the UDT Datetime
--====================================================
DECLARE @Year as SMALLINT
DECLARE @DSTStartDate AS DATETIME
DECLARE @DSTEndDate AS DATETIME
--Get Year
SET @Year = YEAR(@LocalDateTime)
--Get First Possible DST StartDay
IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'
ELSE SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'
--Get DST StartDate
WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate)
--Get First Possible DST EndDate
IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00'
ELSE SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'
--Get DST EndDate
WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate)
--Finally add the DST Offset if needed
RETURN CASE WHEN @LocalDateTime BETWEEN @DSTStartDate AND @DSTEndDate THEN
DATEADD(MINUTE, -60, @UtcDateTime)
ELSE
@UtcDateTime
END
END
GO
Upgrade Script
- Make a backup before running this script!
- Set @Offset & @ApplyDaylightSavings
- Only run once!
begin try
begin transaction;
declare @sql nvarchar(max), @Offset smallint, @ApplyDaylightSavings bit;
set @Offset = -360; --US Central Time, -300 for US Eastern Time, -480 for US West Coast
set @ApplyDaylightSavings = 1; --1 for most US time zones except Arizona which doesn't observer daylight savings, 0 for most time zones outside the US
declare rs cursor for
select 'update [' + a.TABLE_SCHEMA + '].[' + a.TABLE_NAME + '] set [' + a.COLUMN_NAME + '] = dbo.GetUtcDateTime([' + a.COLUMN_NAME + '], ' + cast(@Offset as nvarchar) + ', ' + cast(@ApplyDaylightSavings as nvarchar) + ') ;'
from INFORMATION_SCHEMA.COLUMNS a
inner join INFORMATION_SCHEMA.TABLES b on a.TABLE_SCHEMA = b.TABLE_SCHEMA and a.TABLE_NAME = b.TABLE_NAME
where a.DATA_TYPE = 'datetime' and b.TABLE_TYPE = 'BASE TABLE' ;
open rs;
fetch next from rs into @sql;
while @@FETCH_STATUS = 0 begin
exec sp_executesql @sql;
print @sql;
fetch next from rs into @sql;
end
close rs;
deallocate rs;
commit transaction;
end try
begin catch
close rs;
deallocate rs;
declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
rollback transaction;
raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch
If you have to convert dates other than today to different timezones you have to deal with daylight savings. I wanted a solution that could be done without worrying about database version, without using stored functions and something that could easily be ported to Oracle.
I think Warren is on the right track with getting the correct dates for daylight time, but to make it more useful for multiple time zone and different rules for countries and even the rule that changed in the US between 2006 and 2007, here a variation on the above solution. Notice that this not only has us time zones, but also central Europe. Central Europe follow the last sunday of april and last sunday of october. You will also notice that the US in 2006 follows the old first sunday in april, last sunday in october rule.
This SQL code may look a little ugly, but just copy and paste it into SQL Server and try it. Notice there are 3 section for years, timezones and rules. If you want another year, just add it to the year union. Same for another time zone or rule.
select yr, zone, standard, daylight, rulename, strule, edrule, yrstart, yrend,
dateadd(day, (stdowref + stweekadd), stmonthref) dstlow,
dateadd(day, (eddowref + edweekadd), edmonthref) dsthigh
from (
select yrs.yr, z.zone, z.standard, z.daylight, z.rulename, r.strule, r.edrule,
yrs.yr + '-01-01 00:00:00' yrstart,
yrs.yr + '-12-31 23:59:59' yrend,
yrs.yr + r.stdtpart + ' ' + r.cngtime stmonthref,
yrs.yr + r.eddtpart + ' ' + r.cngtime edmonthref,
case when r.strule in ('1', '2', '3') then case when datepart(dw, yrs.yr + r.stdtpart) = '1' then 0 else 8 - datepart(dw, yrs.yr + r.stdtpart) end
else (datepart(dw, yrs.yr + r.stdtpart) - 1) * -1 end stdowref,
case when r.edrule in ('1', '2', '3') then case when datepart(dw, yrs.yr + r.eddtpart) = '1' then 0 else 8 - datepart(dw, yrs.yr + r.eddtpart) end
else (datepart(dw, yrs.yr + r.eddtpart) - 1) * -1 end eddowref,
datename(dw, yrs.yr + r.stdtpart) stdow,
datename(dw, yrs.yr + r.eddtpart) eddow,
case when r.strule in ('1', '2', '3') then (7 * CAST(r.strule AS Integer)) - 7 else 0 end stweekadd,
case when r.edrule in ('1', '2', '3') then (7 * CAST(r.edrule AS Integer)) - 7 else 0 end edweekadd
from (
select '2005' yr union select '2006' yr -- old us rules
UNION select '2007' yr UNION select '2008' yr UNION select '2009' yr UNION select '2010' yr UNION select '2011' yr
UNION select '2012' yr UNION select '2013' yr UNION select '2014' yr UNION select '2015' yr UNION select '2016' yr
UNION select '2017' yr UNION select '2018' yr UNION select '2019' yr UNION select '2020' yr UNION select '2021' yr
UNION select '2022' yr UNION select '2023' yr UNION select '2024' yr UNION select '2025' yr UNION select '2026' yr
) yrs
cross join (
SELECT 'ET' zone, '-05:00' standard, '-04:00' daylight, 'US' rulename
UNION SELECT 'CT' zone, '-06:00' standard, '-05:00' daylight, 'US' rulename
UNION SELECT 'MT' zone, '-07:00' standard, '-06:00' daylight, 'US' rulename
UNION SELECT 'PT' zone, '-08:00' standard, '-07:00' daylight, 'US' rulename
UNION SELECT 'CET' zone, '+01:00' standard, '+02:00' daylight, 'EU' rulename
) z
join (
SELECT 'US' rulename, '2' strule, '-03-01' stdtpart, '1' edrule, '-11-01' eddtpart, 2007 firstyr, 2099 lastyr, '02:00:00' cngtime
UNION SELECT 'US' rulename, '1' strule, '-04-01' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2006 lastyr, '02:00:00' cngtime
UNION SELECT 'EU' rulename, 'L' strule, '-03-31' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2099 lastyr, '01:00:00' cngtime
) r on r.rulename = z.rulename
and datepart(year, yrs.yr) between firstyr and lastyr
) dstdates
For the rules, use 1, 2, 3 or L for first, second, third or last sunday. The date part gives the month and depending on the rule, the first day of the month or the last day of the month for rule type L.
I put the above query into a view. Now, anytime I want a date with the time zone offset or converted to UTC time, I just join to this view and select get the date in the date format. Instead of datetime, I converted these to datetimeoffset.
select createdon, dst.zone
, case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end pacificoffsettime
, TODATETIMEOFFSET(createdon, case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end) pacifictime
, SWITCHOFFSET(TODATETIMEOFFSET(createdon, case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end), '+00:00') utctime
from (select '2014-01-01 12:00:00' createdon union select '2014-06-01 12:00:00' createdon) photos
left join US_DAYLIGHT_DATES dst on createdon between yrstart and yrend and zone = 'PT'
Here's my quick and dirty version. I know all of my dates were using the US Eastern time zone. You can change the offset or otherwise make it smarter as you need to. I was doing a one-time migration so this was Good Enough.
CREATE FUNCTION [dbo].[ConvertToUtc]
(
@date datetime
)
RETURNS DATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @utcDate datetime;
DECLARE @offset int;
SET @offset = (SELECT CASE WHEN
@date BETWEEN '1987-04-05 02:00 AM' AND '1987-10-25 02:00 AM'
OR @date BETWEEN '1988-04-03 02:00 AM' AND '1988-10-30 02:00 AM'
OR @date BETWEEN '1989-04-02 02:00 AM' AND '1989-10-29 02:00 AM'
OR @date BETWEEN '1990-04-01 02:00 AM' AND '1990-10-28 02:00 AM'
OR @date BETWEEN '1991-04-07 02:00 AM' AND '1991-10-27 02:00 AM'
OR @date BETWEEN '1992-04-05 02:00 AM' AND '1992-10-25 02:00 AM'
OR @date BETWEEN '1993-04-04 02:00 AM' AND '1993-10-31 02:00 AM'
OR @date BETWEEN '1994-04-03 02:00 AM' AND '1994-10-30 02:00 AM'
OR @date BETWEEN '1995-04-02 02:00 AM' AND '1995-10-29 02:00 AM'
OR @date BETWEEN '1996-04-07 02:00 AM' AND '1996-10-27 02:00 AM'
OR @date BETWEEN '1997-04-06 02:00 AM' AND '1997-10-26 02:00 AM'
OR @date BETWEEN '1998-04-05 02:00 AM' AND '1998-10-25 02:00 AM'
OR @date BETWEEN '1999-04-04 02:00 AM' AND '1999-10-31 02:00 AM'
OR @date BETWEEN '2000-04-02 02:00 AM' AND '2000-10-29 02:00 AM'
OR @date BETWEEN '2001-04-01 02:00 AM' AND '2001-10-28 02:00 AM'
OR @date BETWEEN '2002-04-07 02:00 AM' AND '2002-10-27 02:00 AM'
OR @date BETWEEN '2003-04-06 02:00 AM' AND '2003-10-26 02:00 AM'
OR @date BETWEEN '2004-04-04 02:00 AM' AND '2004-10-31 02:00 AM'
OR @date BETWEEN '2005-04-03 02:00 AM' AND '2005-10-30 02:00 AM'
OR @date BETWEEN '2006-04-02 02:00 AM' AND '2006-10-29 02:00 AM'
OR @date BETWEEN '2007-03-11 02:00 AM' AND '2007-11-04 02:00 AM'
OR @date BETWEEN '2008-03-09 02:00 AM' AND '2008-11-02 02:00 AM'
OR @date BETWEEN '2009-03-08 02:00 AM' AND '2009-11-01 02:00 AM'
OR @date BETWEEN '2010-03-14 02:00 AM' AND '2010-11-07 02:00 AM'
OR @date BETWEEN '2011-03-13 02:00 AM' AND '2011-11-06 02:00 AM'
OR @date BETWEEN '2012-03-11 02:00 AM' AND '2012-11-04 02:00 AM'
OR @date BETWEEN '2013-03-10 02:00 AM' AND '2013-11-03 02:00 AM'
OR @date BETWEEN '2014-03-09 02:00 AM' AND '2014-11-02 02:00 AM'
OR @date BETWEEN '2015-03-08 02:00 AM' AND '2015-11-01 02:00 AM'
OR @date BETWEEN '2016-03-13 02:00 AM' AND '2016-11-06 02:00 AM'
OR @date BETWEEN '2017-03-12 02:00 AM' AND '2017-11-05 02:00 AM'
OR @date BETWEEN '2018-03-11 02:00 AM' AND '2018-11-04 02:00 AM'
OR @date BETWEEN '2019-03-10 02:00 AM' AND '2019-11-03 02:00 AM'
OR @date BETWEEN '2020-03-08 02:00 AM' AND '2020-11-01 02:00 AM'
OR @date BETWEEN '2021-03-14 02:00 AM' AND '2021-11-07 02:00 AM'
THEN 4
ELSE 5 END);
SELECT @utcDate = DATEADD(hh, @offset, @date)
RETURN @utcDate;
END
The following should work as it calculates difference between DATE and UTCDATE for the server you are running and uses that offset to calculate the UTC equivalent of any date you pass to it. In my example, I am trying to convert UTC equivalent for '1-nov-2012 06:00' in Adelaide, Australia where UTC offset is -630 minutes, which when added to any date will result in UTC equivalent of any local date.
select DATEADD(MINUTE, DATEDIFF(MINUTE, GETDATE(), GETUTCDATE()), '1-nov-2012 06:00')
Unless I missed something above (possible), all of the methods above are flawed in that they don't take the overlap when switching from daylight savings (say EDT) to standard time (say EST) into account. A (very verbose) example:
[1] EDT 2016-11-06 00:59 - UTC 2016-11-06 04:59
[2] EDT 2016-11-06 01:00 - UTC 2016-11-06 05:00
[3] EDT 2016-11-06 01:30 - UTC 2016-11-06 05:30
[4] EDT 2016-11-06 01:59 - UTC 2016-11-06 05:59
[5] EST 2016-11-06 01:00 - UTC 2016-11-06 06:00
[6] EST 2016-11-06 01:30 - UTC 2016-11-06 06:30
[7] EST 2016-11-06 01:59 - UTC 2016-11-06 06:59
[8] EST 2016-11-06 02:00 - UTC 2016-11-06 07:00
Simple hour offsets based on date and time won't cut it. If you don't know if the local time was recorded in EDT or EST between 01:00 and 01:59, you won't have a clue! Let's use 01:30 for example: if you find later times in the range 01:31 through 01:59 BEFORE it, you won't know if the 01:30 you're looking at is [3 or [6. In this case, you can get the correct UTC time with a bit of coding be looking at previous entries (not fun in SQL), and this is the BEST case...
Say you have the following local times recorded, and didn't dedicate a bit to indicate EDT or EST:
UTC time UTC time UTC time
if [2] and [3] if [2] and [3] if [2] before
local time before switch after switch and [3] after
[1] 2016-11-06 00:43 04:43 04:43 04:43
[2] 2016-11-06 01:15 05:15 06:15 05:15
[3] 2016-11-06 01:45 05:45 06:45 06:45
[4] 2016-11-06 03:25 07:25 07:25 07:25
Times [2] and [3] may be in the 5 AM timeframe, the 6 AM timeframe, or one in the 5 AM and the other in the 6 AM timeframe . . . In other words: you are hosed, and must throw out all readings between 01:00:00 and 01:59:59. In this circumstance, there is absolutely no way to resolve the actual UTC time!
Depending on how far back you need to go, you can build a table of daylight savings times and then join the table and do a dst-sensitive conversion. This particular one converts from EST to GMT (i.e. uses offsets of 5 and 4).
select createdon, dateadd(hour, case when dstlow is null then 5 else 4 end, createdon) as gmt
from photos
left outer join (
SELECT {ts '2009-03-08 02:00:00'} as dstlow, {ts '2009-11-01 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2010-03-14 02:00:00'} as dstlow, {ts '2010-11-07 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2011-03-13 02:00:00'} as dstlow, {ts '2011-11-06 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2012-03-11 02:00:00'} as dstlow, {ts '2012-11-04 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2013-03-10 02:00:00'} as dstlow, {ts '2013-11-03 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2014-03-09 02:00:00'} as dstlow, {ts '2014-11-02 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2015-03-08 02:00:00'} as dstlow, {ts '2015-11-01 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2016-03-13 02:00:00'} as dstlow, {ts '2016-11-06 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2017-03-12 02:00:00'} as dstlow, {ts '2017-11-05 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2018-03-11 02:00:00'} as dstlow, {ts '2018-11-04 02:00:00'} as dsthigh
) dst
on createdon >= dstlow and createdon < dsthigh
We can convert ServerZone DateTime to UTC and UTC to ServerZone DateTime
Simply run the following scripts to understand the conversion then modify as what you need
--Get Server's TimeZone
DECLARE @ServerTimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@ServerTimeZone OUT
-- ServerZone to UTC DATETIME
DECLARE @CurrentServerZoneDateTime DATETIME = GETDATE()
DECLARE @UTCDateTime DATETIME = @CurrentServerZoneDateTime AT TIME ZONE @ServerTimeZone AT TIME ZONE 'UTC'
--(OR)
--DECLARE @UTCDateTime DATETIME = GETUTCDATE()
SELECT @CurrentServerZoneDateTime AS CURRENTZONEDATE,@UTCDateTime AS UTCDATE
-- UTC to ServerZone DATETIME
SET @CurrentServerZoneDateTime = @UTCDateTime AT TIME ZONE 'UTC' AT TIME ZONE @ServerTimeZone
SELECT @UTCDateTime AS UTCDATE,@CurrentServerZoneDateTime AS CURRENTZONEDATE
Note: This(AT TIME ZONE) working on only SQL Server 2016+ and this advantage is automatically considering Daylight while converting to particular Time zone
I'm a bit late to the game but I needed to do something like this on SQL 2012, I haven't fully tested it yet but here is what I came up with.
CREATE FUNCTION SMS.fnConvertUTC
(
@DateCST datetime
)
RETURNS DATETIME
AS
BEGIN
RETURN
CASE
WHEN @DateCST
BETWEEN
CASE WHEN @DateCST > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-MAR-14 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-MAR-14 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-APR-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-APR-07 02:00' ) + 1 END
AND
CASE WHEN @DateCST > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-NOV-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-NOV-07 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-OCT-31 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-OCT-31 02:00' ) + 1 END
THEN DATEADD(HOUR,4,@DateCST)
ELSE DATEADD(HOUR,5,@DateCST)
END
END
Above someone posted a static list DST dates so I wrote the below query to compare this code's output to that list... so far it looks correct.
;WITH DT AS
(
SELECT MyDate = GETDATE()
UNION ALL
SELECT MyDate = DATEADD(YEAR,-1,MyDate) FROM DT
WHERE DATEADD(YEAR,-1,MyDate) > DATEADD(YEAR, -30, GETDATE())
)
SELECT
SpringForward = CASE
WHEN MyDate > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-MAR-14 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-MAR-14 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-APR-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-APR-07 02:00' ) + 1 END
, FallBackward = CASE
WHEN MyDate > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-NOV-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-NOV-07 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-OCT-31 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-OCT-31 02:00' ) + 1 END
FROM DT
ORDER BY 1 DESC
SpringForward FallBackward
---------------- ----------------
2020-03-08 02:00 2020-11-01 02:00
2019-03-10 02:00 2019-11-03 02:00
2018-03-11 02:00 2018-11-04 02:00
2017-03-12 02:00 2017-11-05 02:00
2016-03-13 02:00 2016-11-06 02:00
2015-03-08 02:00 2015-11-01 02:00
2014-03-09 02:00 2014-11-02 02:00
2013-03-10 02:00 2013-11-03 02:00
2012-03-11 02:00 2012-11-04 02:00
2011-03-13 02:00 2011-11-06 02:00
2010-03-14 02:00 2010-11-07 02:00
2009-03-08 02:00 2009-11-01 02:00
2008-03-09 02:00 2008-11-02 02:00
2007-03-11 02:00 2007-11-04 02:00
2006-04-02 02:00 2006-10-29 02:00
2005-04-03 02:00 2005-10-30 02:00
2004-04-04 02:00 2004-10-31 02:00
2003-04-06 02:00 2003-10-26 02:00
2002-04-07 02:00 2002-10-27 02:00
2001-04-01 02:00 2001-10-28 02:00
2000-04-02 02:00 2000-10-29 02:00
1999-04-04 02:00 1999-10-31 02:00
1998-04-05 02:00 1998-10-25 02:00
1997-04-06 02:00 1997-10-26 02:00
1996-04-07 02:00 1996-10-27 02:00
1995-04-02 02:00 1995-10-29 02:00
1994-04-03 02:00 1994-10-30 02:00
1993-04-04 02:00 1993-10-31 02:00
1992-04-05 02:00 1992-10-25 02:00
1991-04-07 02:00 1991-10-27 02:00
(30 row(s) affected)
加载中,请稍侯......
精彩评论