May 18, 2018 at 6:27 am
I recently found a nifty Excel Formula that worked out if a datetime was UTC or BST (British Summer time) and adjusted the input accordingly to output UTC
Here's the formula, where C3 contains the date to convert (or leave alone if already UTC, i.e. not BST)
=c3-(PRODUCT(c3-DATE(YEAR(c3),{4,11},1-WEEKDAY(DATE(YEAR(c3),{4,11},0)))-1/24)<0)/24
The explanation & source of this is:
The general principle is as follows:
The DATE part generates an array of two dates - both the start and end of BST for the year in question. These dates are subtracted from the datetime in C3
(with an adjustment for the time of day of the BST/GMT switch). If the resultant numbers are both positive then C3 is after the end of BST,
if both are negative then C3 is before BST.....if you have one of each then it must be within the BST period.
Using PRODUCT multiplies the two numbers, so a negative result from the Product of the 2 indicates 1 of each, i.e. within BST and so one hour (1/24) is subtracted.
The dates themselves are generated using a variation of this formula
=DATE(YEAR(C3),4,1-WEEKDAY(DATE(YEAR(C3),4,0)))
This finds the last Sunday in March (by finding the first Sunday in April and subtracting 7), so we can extend it to find both the last Sunday in March and the last Sunday in October by replacing 4 with {4,11}
https://www.mrexcel.com/forum/excel-questions/417620-how-check-datetime-gmt-bst.html
I think if I could convert these functions to SQL this could be a perfomant replacement for a UDF I currently use to convert Times to UTC if required.
The reason I need to do this is because in one particular db in our estate, it uses UTC to record when events happen. So to search the db for records in a given window I have to do something like
Select * from someTable
WHERE CreatedDate >= udfLocalTimeToUtc(@from, 0)
AND CreatedDate < udfLocalTimeToUtc(@To, 0)
My problem is I do not know Excel Formulas well enough to work out the equivelent SQL Functions. I'm sure for instance that the DATE function to get the last day of March cound be replaced with EOMONTH ('1 Apr 2018',-1) but I am stuggling with the rest of it.
Any Excel Experts out there?
Dave J
May 18, 2018 at 8:22 am
Are you familiar with the DATEFROMPARTS and/or DATETIMEFROMPARTS functions in SQL Server? You can specify the year, month, and day for the former, and for the latter, year, month, day, hours, minute, seconds, and milliseconds. There are also similar functions for datetime2 and datetimeoffset, as well as smalldatetime (which stops at the minute). These functions were available beginning with SQL Server 2012. What's available on the Excel side also depends on the version of Excel, but if you have EOMONTH, it's not too hard to convert that into T-SQL.
DECLARE @Today AS date = GETDATE();
SELECT CONVERT(date, DATEADD(day, 0 - DATEPART(day, @Today), DATEADD(month, 1, @Today))) AS NewDate;
You could also change the 1 in the 2nd DATEADD to any number to replicate the number of months EOMONTH allows you to specify.
Does that help?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 18, 2018 at 8:49 am
That's a thought! And I have realised I didn't post where I got to. D'oh!
Here's where I am:
declare @c3 datetime = '2018-03-28 01:00', @c5 VARCHAR(25)
/*
=c3-(
PRODUCT(
c3-DATE(
YEAR(c3),{4,11},1-WEEKDAY(DATE(YEAR(c3),{4,11},0))
)-1/24
)<0)/24
*/
SELECT @c5 = @c3-IIf(CAST('1 apr ' + CAST(YEAR(@c3) AS VARCHAR(4)) AS DATETIME) - DATEPART(dw,EOMONTH('1 apr ' + CAST(YEAR(@c3) AS VARCHAR(4)),-1))-1/24.0<0,@c3,DATEADD(hh,-1,@c3))
SELECT @c5
May 18, 2018 at 8:59 am
David Jackson - Friday, May 18, 2018 8:49 AMThat's a thought! And I have realised I didn't post where I got to. D'oh!Here's where I am:
declare @c3 datetime = '2018-03-28 01:00', @c5 VARCHAR(25)/*
=c3-(
PRODUCT(
c3-DATE(
YEAR(c3),{4,11},1-WEEKDAY(DATE(YEAR(c3),{4,11},0))
)-1/24
)<0)/24
*/
SELECT @c5 = @c3-IIf(CAST('1 apr ' + CAST(YEAR(@c3) AS VARCHAR(4)) AS DATETIME) - DATEPART(dw,EOMONTH('1 apr ' + CAST(YEAR(@c3) AS VARCHAR(4)),-1))-1/24.0<0,@c3,DATEADD(hh,-1,@c3))SELECT @c5
If you are going to convert character strings into dates, it's probably easier to use DATETIMEFROMPARTS or DATEFROMPARTS. One should also generally stay away from storing dates as character strings. Also, if you must take a string directly to a date, then the best choice of format is YYYYMMDD, or YYYY-MM-DD. The first is best because it's universal within SQL Server as to interpretation. The latter appears to be subject to the DATEFORMAT setting. I'm fine with getting corrected if I don't have that right... it's what I think I remember....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 18, 2018 at 9:28 am
I Think the key to this is How Excel is treating the string {4,11}
I think the PRODUCT is acting on the 2 values this produces
Head currently blown!
May 18, 2018 at 9:31 am
sgmunson - Friday, May 18, 2018 8:59 AMDavid Jackson - Friday, May 18, 2018 8:49 AMThat's a thought! And I have realised I didn't post where I got to. D'oh!Here's where I am:
declare @c3 datetime = '2018-03-28 01:00', @c5 VARCHAR(25)/*
=c3-(
PRODUCT(
c3-DATE(
YEAR(c3),{4,11},1-WEEKDAY(DATE(YEAR(c3),{4,11},0))
)-1/24
)<0)/24
*/
SELECT @c5 = @c3-IIf(CAST('1 apr ' + CAST(YEAR(@c3) AS VARCHAR(4)) AS DATETIME) - DATEPART(dw,EOMONTH('1 apr ' + CAST(YEAR(@c3) AS VARCHAR(4)),-1))-1/24.0<0,@c3,DATEADD(hh,-1,@c3))SELECT @c5
If you are going to convert character strings into dates, it's probably easier to use DATETIMEFROMPARTS or DATEFROMPARTS. One should also generally stay away from storing dates as character strings. Also, if you must take a string directly to a date, then the best choice of format is YYYYMMDD, or YYYY-MM-DD. The first is best because it's universal within SQL Server as to interpretation. The latter appears to be subject to the DATEFORMAT setting. I'm fine with getting corrected if I don't have that right... it's what I think I remember....
This is just an example of trying to get the formula replicated 🙂 In production the @C3 variable would actually be a datetime column, but thanks for the input. I do appreciate it and I'm sure that this *can* be done, just not sure how yet....
Dave
May 18, 2018 at 9:33 am
David Jackson - Friday, May 18, 2018 9:28 AMI Think the key to this is How Excel is treating the string {4,11}I think the PRODUCT is acting on the 2 values this produces
Head currently blown!
Probably. I've never seen the curly braces in use in any Excel formula I've ever seen, so maybe Google search that...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 18, 2018 at 9:54 am
I'll have to partially retract that last post... I looked up curly braces and found it's an "array formula" in Excel. I actually HAVE seen that before, but it was long enough ago that I had forgotten all about it. Reproducing an array formula in T-SQL isn't necessarily going to be difficult, but it's not going to be a mere formula, as the closest thing to an array in SQL Server is either a table or a table variable, neither of which have any built-in "array" functions. You'll need queries and aggregates or some fancy WHERE clauses to reproduce that stuff.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 18, 2018 at 11:18 am
Turns out that the formula in question:=c3-(PRODUCT(c3-DATE(YEAR(c3),{4,11},1-WEEKDAY(DATE(YEAR(c3),{4,11},0)))-1/24)<0)/24
just takes any given datetime value, and within that value's year, subtracts one hour whenever the value is between March 30th at 1 am and October 26th at 12:59:59 am.
And that, could be done in a simple CASE statement:SELECT
CASE
WHEN DATETIMEFROMPARTS(YEAR(col), MONTH(col), DATEPART(day, col), DATEPART(hour, col), DATEPART(second, col), DATEPART(ms, col)) BETWEEN DATETIMEFROMPARTS(YEAR(col), 3, 26, 1, 0, 0, 0) AND DATETIMEFROMPARTS(YEAR(col), 10, 26, 0, 59, 59, 997) THEN DATEADD(hour, -1, col)
ELSE col
END AS ResultCol
FROM YourTableName
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 21, 2018 at 3:33 am
The power of the function is that it works out the last Sunday of March and October regardless of the year of the date passed in, but you have given food for thought on how to make this work.
May 21, 2018 at 6:39 am
David Jackson - Monday, May 21, 2018 3:33 AMThe power of the function is that it works out the last Sunday of March and October regardless of the year of the date passed in, but you have given food for thought on how to make this work.
If that is the purpose of the function, then it's not working correctly. It consistently uses the dates I referred to, regardless of the input year. Most daylight savings time types of time adjustments world-wide operate on the basis of a specific date range, but as I'm not familiar with the UK's system, I can't speak to it. If it's actually supposed to be the last Sunday in March or October that are the "border" dates, then this function doesn't work correctly.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 21, 2018 at 7:16 am
I took your code and tried this:
DECLARE @c3 DATETIME = '2014-03-01 04:00:00.000';
/*
UK DST dates for about 10 years
2015-03-29 01:00:00.000 2015-10-25 02:00:00.000
2016-03-27 01:00:00.000 2016-10-30 02:00:00.000
2017-03-26 01:00:00.000 2017-10-29 02:00:00.000
2018-03-25 01:00:00.000 2018-10-28 02:00:00.000
2019-03-31 01:00:00.000 2019-10-27 02:00:00.000
2020-03-29 01:00:00.000 2020-10-25 02:00:00.000
2021-03-28 01:00:00.000 2021-10-31 02:00:00.000
2022-03-27 01:00:00.000 2022-10-30 02:00:00.000
2023-03-26 01:00:00.000 2023-10-29 02:00:00.000
2024-03-31 01:00:00.000 2024-10-27 02:00:00.000
*/
/*
=c3-(
PRODUCT(
c3-DATE(
YEAR(c3),{4,11},1-WEEKDAY(DATE(YEAR(c3),{4,11},0))
)-1/24
)<0)/24
*/
SELECT CASE
WHEN DATETIMEFROMPARTS(
YEAR(@c3),
MONTH(@c3),
DATEPART(DAY, @c3),
DATEPART(HOUR, @c3),
DATEPART(MINUTE, @c3),
DATEPART(SECOND, @c3),
DATEPART(ms, @c3)) BETWEEN DATETIMEFROMPARTS(
YEAR(@c3),
3,
DATEPART(
DAY,
DATEADD(
DAY,
DATEDIFF(
DAY,
'19000107',
DATEADD(MONTH, DATEDIFF(MONTH, 0, @c3), 30)) / 7 * 7,
'19000107')),
1,
0,
0,
0) AND DATETIMEFROMPARTS(
YEAR(@c3),
10,
DATEPART(
DAY,
DATEADD(
DAY,
DATEDIFF(
DAY,
'19000107',
DATEADD(
MONTH,
DATEDIFF(MONTH, 0, @C3),
30)) / 7 * 7,
'19000107')),
1,
59,
59,
997) THEN DATEADD(HOUR, -1, @c3)
ELSE @c3 END Result;
The nicely laid out code, courtesy SQL Prompt seems to get lost, (Only in preview), but this is doing what I want it to do I think. Playing with it while restoring a Test db 🙂
P.S. You may have forgotten you need to enter array formulas in Excel with a CTRL-ENTER keystroke so they work correctly. AFAICS, it works...
Dave
May 21, 2018 at 8:12 am
David Jackson - Monday, May 21, 2018 7:16 AMI took your code and tried this:
DECLARE @c3 DATETIME = '2014-03-01 04:00:00.000';
/*
UK DST dates for about 10 years2015-03-29 01:00:00.000 2015-10-25 02:00:00.000
2016-03-27 01:00:00.000 2016-10-30 02:00:00.000
2017-03-26 01:00:00.000 2017-10-29 02:00:00.000
2018-03-25 01:00:00.000 2018-10-28 02:00:00.000
2019-03-31 01:00:00.000 2019-10-27 02:00:00.000
2020-03-29 01:00:00.000 2020-10-25 02:00:00.000
2021-03-28 01:00:00.000 2021-10-31 02:00:00.000
2022-03-27 01:00:00.000 2022-10-30 02:00:00.000
2023-03-26 01:00:00.000 2023-10-29 02:00:00.000
2024-03-31 01:00:00.000 2024-10-27 02:00:00.000*/
/*
=c3-(
PRODUCT(
c3-DATE(
YEAR(c3),{4,11},1-WEEKDAY(DATE(YEAR(c3),{4,11},0))
)-1/24
)<0)/24
*/SELECT CASE
WHEN DATETIMEFROMPARTS(
YEAR(@c3),
MONTH(@c3),
DATEPART(DAY, @c3),
DATEPART(HOUR, @c3),
DATEPART(MINUTE, @c3),
DATEPART(SECOND, @c3),
DATEPART(ms, @c3)) BETWEEN DATETIMEFROMPARTS(
YEAR(@c3),
3,
DATEPART(
DAY,
DATEADD(
DAY,
DATEDIFF(
DAY,
'19000107',
DATEADD(MONTH, DATEDIFF(MONTH, 0, @c3), 30)) / 7 * 7,
'19000107')),
1,
0,
0,
0) AND DATETIMEFROMPARTS(
YEAR(@c3),
10,
DATEPART(
DAY,
DATEADD(
DAY,
DATEDIFF(
DAY,
'19000107',
DATEADD(
MONTH,
DATEDIFF(MONTH, 0, @C3),
30)) / 7 * 7,
'19000107')),
1,
59,
59,
997) THEN DATEADD(HOUR, -1, @c3)
ELSE @c3 END Result;The nicely laid out code, courtesy SQL Prompt
seems to get lost,(Only in preview), but this is doing what I want it to do I think. Playing with it while restoring a Test db 🙂P.S. You may have forgotten you need to enter array formulas in Excel with a CTRL-ENTER keystroke so they work correctly. AFAICS, it works...
Dave
Back when I looked up the curly braces, I had at that point discovered that they were alleged to be entered using CTRL+SHIFT+ENTER. When I would do that to the formula, it ended up with braces around the entire formula, so I didn't go any further as it was "appearing to work" without that. Apparently, using just CTRL+ENTER works for formulas where the entire formula is not array based. I now get differences in the results that I wasn't getting before, so thanks for "learnin' me" something new on array formulas (either that or it was just that I forgot that little detail entirely). Determining the last Sunday in March and October is easily achieved, and I'd guess you've probably managed it. I'm going to post my date creating test harness for now, and if I have time, I'll see what I can do with a T-SQL formula that gets the last Sunday for any given month and year.WITH E AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
THE_YEARS AS (
SELECT TOP (8100) DATEADD(year, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '18990101') AS THE_DATE
FROM E AS E1
CROSS APPLY E AS E2
CROSS APPLY E AS E3
CROSS APPLY E AS E4
)
SELECT Y.THE_DATE,
CASE
WHEN DATETIMEFROMPARTS(YEAR(Z.A), MONTH(Z.A), DATEPART(day, Z.A), DATEPART(hour, Z.A), DATEPART(minute, Z.A), DATEPART(second, Z.A), DATEPART(ms, Z.A)) BETWEEN DATETIMEFROMPARTS(YEAR(Z.A), 3, 30, 1, 0, 0, 0) AND DATETIMEFROMPARTS(YEAR(Z.A), 10, 26, 0, 59, 59, 997) THEN DATEADD(hour, -1, Z.A)
ELSE Z.A
END AS MARCH_30_12_AM,
CASE
WHEN DATETIMEFROMPARTS(YEAR(Z.B), MONTH(Z.B), DATEPART(day, Z.B), DATEPART(hour, Z.B), DATEPART(minute, Z.B), DATEPART(second, Z.B), DATEPART(ms, Z.B)) BETWEEN DATETIMEFROMPARTS(YEAR(Z.B), 3, 30, 1, 0, 0, 0) AND DATETIMEFROMPARTS(YEAR(Z.B), 10, 26, 0, 59, 59, 997) THEN DATEADD(hour, -1, Z.B)
ELSE Z.B
END AS MARCH_30_1_AM,
CASE
WHEN DATETIMEFROMPARTS(YEAR(Z.C), MONTH(Z.C), DATEPART(day, Z.C), DATEPART(hour, Z.C), DATEPART(minute, Z.C), DATEPART(second, Z.C), DATEPART(ms, Z.C)) BETWEEN DATETIMEFROMPARTS(YEAR(Z.C), 3, 30, 1, 0, 0, 0) AND DATETIMEFROMPARTS(YEAR(Z.C), 10, 26, 0, 59, 59, 997) THEN DATEADD(hour, -1, Z.C)
ELSE Z.C
END AS MARCH_30_2_AM,
CASE
WHEN DATETIMEFROMPARTS(YEAR(Z.D), MONTH(Z.D), DATEPART(day, Z.D), DATEPART(hour, Z.D), DATEPART(minute, Z.D), DATEPART(second, Z.D), DATEPART(ms, Z.D)) BETWEEN DATETIMEFROMPARTS(YEAR(Z.D), 3, 30, 1, 0, 0, 0) AND DATETIMEFROMPARTS(YEAR(Z.D), 10, 26, 0, 59, 59, 997) THEN DATEADD(hour, -1, Z.D)
ELSE Z.D
END AS OCT_26_12_AM,
CASE
WHEN DATETIMEFROMPARTS(YEAR(Z.E), MONTH(Z.E), DATEPART(day, Z.E), DATEPART(hour, Z.E), DATEPART(minute, Z.E), DATEPART(second, Z.E), DATEPART(ms, Z.E)) BETWEEN DATETIMEFROMPARTS(YEAR(Z.E), 3, 30, 1, 0, 0, 0) AND DATETIMEFROMPARTS(YEAR(Z.E), 10, 26, 0, 59, 59, 997) THEN DATEADD(hour, -1, Z.E)
ELSE Z.E
END AS OCT_26_1_AM,
CASE
WHEN DATETIMEFROMPARTS(YEAR(Z.F), MONTH(Z.F), DATEPART(day, Z.F), DATEPART(hour, Z.F), DATEPART(minute, Z.F), DATEPART(second, Z.F), DATEPART(ms, Z.F)) BETWEEN DATETIMEFROMPARTS(YEAR(Z.F), 3, 30, 1, 0, 0, 0) AND DATETIMEFROMPARTS(YEAR(Z.F), 10, 26, 0, 59, 59, 997) THEN DATEADD(hour, -1, Z.F)
ELSE Z.F
END AS OCT_26_2_AM
FROM THE_YEARS AS Y
CROSS APPLY (
SELECT DATEADD(hour, 0, DATEADD(day, 29, DATEADD(month, 2, Y.THE_DATE))) AS A,
DATEADD(hour, 1, DATEADD(day, 29, DATEADD(month, 2, Y.THE_DATE))) AS B,
DATEADD(hour, 2, DATEADD(day, 29, DATEADD(month, 2, Y.THE_DATE))) AS C,
DATEADD(hour, 0, DATEADD(day, 25, DATEADD(month, 9, Y.THE_DATE))) AS D,
DATEADD(hour, 1, DATEADD(day, 25, DATEADD(month, 9, Y.THE_DATE))) AS E,
DATEADD(hour, 2, DATEADD(day, 25, DATEADD(month, 9, Y.THE_DATE))) AS F
) AS Z
ORDER BY Y.THE_DATE;
Note that the formula I had previously posted is the part of the code that will need updating to work correctly.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 21, 2018 at 9:30 am
And here's the rest of the code that handles the last Sunday in both March and October, with a test rig to produce results for the entire period from date 0 (1/1/1900) through the year 9999:WITH E AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
THE_YEARS AS (
SELECT TOP (8100) DATEADD(year, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '18990101') AS THE_DATE
FROM E AS E1
CROSS APPLY E AS E2
CROSS APPLY E AS E3
CROSS APPLY E AS E4
),
DATE_RANGES AS (
SELECT Y.THE_DATE,
DATEADD(hour, 1, DATEADD(day, W.WDM, CONVERT(datetime, DATEFROMPARTS(YEAR(Y.THE_DATE), 4, 1)))) AS STARTING_DATE,
DATEADD(hour, 1, DATEADD(day, W.WDO, CONVERT(datetime, DATEFROMPARTS(YEAR(Y.THE_DATE), 11, 1)))) AS ENDING_DATE
FROM THE_YEARS AS Y
CROSS APPLY (
SELECT DATEPART(weekday, DATEFROMPARTS(YEAR(Y.THE_DATE), 4, 1)) AS WD_M,
DATEPART(weekday, DATEFROMPARTS(YEAR(Y.THE_DATE), 1, 1)) AS WD_O
) AS WD
CROSS APPLY (
SELECT 1 - ISNULL(NULLIF(WD.WD_M, 1), 8) AS WDM,
1 - ISNULL(NULLIF(WD.WD_O, 1), 8) AS WDO
) AS W
)
SELECT YEAR(Y.THE_DATE) AS THE_YEAR,
R.STARTING_DATE,
R.ENDING_DATE,
CASE WHEN Z.A1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.A1) ELSE Z.A1 END AS MAR_25_12_AM,
CASE WHEN Z.B1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.B1) ELSE Z.B1 END AS MAR_25_01_AM,
CASE WHEN Z.C1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.C1) ELSE Z.C1 END AS MAR_26_12_AM,
CASE WHEN Z.D1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.D1) ELSE Z.D1 END AS MAR_26_01_AM,
CASE WHEN Z.E1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.E1) ELSE Z.E1 END AS MAR_27_12_AM,
CASE WHEN Z.F1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.F1) ELSE Z.F1 END AS MAR_27_01_AM,
CASE WHEN Z.G1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.G1) ELSE Z.G1 END AS MAR_28_12_AM,
CASE WHEN Z.H1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.H1) ELSE Z.H1 END AS MAR_28_01_AM,
CASE WHEN Z.I1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.I1) ELSE Z.I1 END AS MAR_29_12_AM,
CASE WHEN Z.J1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.J1) ELSE Z.J1 END AS MAR_29_01_AM,
CASE WHEN Z.K1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.K1) ELSE Z.K1 END AS MAR_30_12_AM,
CASE WHEN Z.L1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.L1) ELSE Z.L1 END AS MAR_30_01_AM,
CASE WHEN Z.M1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.M1) ELSE Z.M1 END AS MAR_31_12_AM,
CASE WHEN Z.N1 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.N1) ELSE Z.N1 END AS MAR_31_01_AM,
CASE WHEN Z.A2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.A2) ELSE Z.A2 END AS OCT_25_12_AM,
CASE WHEN Z.B2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.B2) ELSE Z.B2 END AS OCT_25_01_AM,
CASE WHEN Z.C2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.C2) ELSE Z.C2 END AS OCT_26_12_AM,
CASE WHEN Z.D2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.D2) ELSE Z.D2 END AS OCT_26_01_AM,
CASE WHEN Z.E2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.E2) ELSE Z.E2 END AS OCT_27_12_AM,
CASE WHEN Z.F2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.F2) ELSE Z.F2 END AS OCT_27_01_AM,
CASE WHEN Z.G2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.G2) ELSE Z.G2 END AS OCT_28_12_AM,
CASE WHEN Z.H2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.H2) ELSE Z.H2 END AS OCT_28_01_AM,
CASE WHEN Z.I2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.I2) ELSE Z.I2 END AS OCT_29_12_AM,
CASE WHEN Z.J2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.J2) ELSE Z.J2 END AS OCT_29_01_AM,
CASE WHEN Z.K2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.K2) ELSE Z.K2 END AS OCT_30_12_AM,
CASE WHEN Z.L2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.L2) ELSE Z.L2 END AS OCT_30_01_AM,
CASE WHEN Z.M2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.M2) ELSE Z.M2 END AS OCT_31_12_AM,
CASE WHEN Z.N2 BETWEEN R.STARTING_DATE AND R.ENDING_DATE THEN DATEADD(hour, -1, Z.N2) ELSE Z.N2 END AS OCT_31_01_AM
FROM THE_YEARS AS Y
CROSS APPLY (
SELECT DATEADD(hour, 0, DATEADD(day, 24, DATEADD(month, 2, Y.THE_DATE))) AS A1,
DATEADD(hour, 1, DATEADD(day, 24, DATEADD(month, 2, Y.THE_DATE))) AS B1,
DATEADD(hour, 0, DATEADD(day, 25, DATEADD(month, 2, Y.THE_DATE))) AS C1,
DATEADD(hour, 1, DATEADD(day, 25, DATEADD(month, 2, Y.THE_DATE))) AS D1,
DATEADD(hour, 0, DATEADD(day, 26, DATEADD(month, 2, Y.THE_DATE))) AS E1,
DATEADD(hour, 1, DATEADD(day, 26, DATEADD(month, 2, Y.THE_DATE))) AS F1,
DATEADD(hour, 0, DATEADD(day, 27, DATEADD(month, 2, Y.THE_DATE))) AS G1,
DATEADD(hour, 1, DATEADD(day, 27, DATEADD(month, 2, Y.THE_DATE))) AS H1,
DATEADD(hour, 0, DATEADD(day, 28, DATEADD(month, 2, Y.THE_DATE))) AS I1,
DATEADD(hour, 1, DATEADD(day, 28, DATEADD(month, 2, Y.THE_DATE))) AS J1,
DATEADD(hour, 0, DATEADD(day, 29, DATEADD(month, 2, Y.THE_DATE))) AS K1,
DATEADD(hour, 1, DATEADD(day, 29, DATEADD(month, 2, Y.THE_DATE))) AS L1,
DATEADD(hour, 0, DATEADD(day, 30, DATEADD(month, 2, Y.THE_DATE))) AS M1,
DATEADD(hour, 1, DATEADD(day, 30, DATEADD(month, 2, Y.THE_DATE))) AS N1,
DATEADD(hour, 0, DATEADD(day, 24, DATEADD(month, 9, Y.THE_DATE))) AS A2,
DATEADD(hour, 1, DATEADD(day, 24, DATEADD(month, 9, Y.THE_DATE))) AS B2,
DATEADD(hour, 0, DATEADD(day, 25, DATEADD(month, 9, Y.THE_DATE))) AS C2,
DATEADD(hour, 1, DATEADD(day, 25, DATEADD(month, 9, Y.THE_DATE))) AS D2,
DATEADD(hour, 0, DATEADD(day, 26, DATEADD(month, 9, Y.THE_DATE))) AS E2,
DATEADD(hour, 1, DATEADD(day, 26, DATEADD(month, 9, Y.THE_DATE))) AS F2,
DATEADD(hour, 0, DATEADD(day, 27, DATEADD(month, 9, Y.THE_DATE))) AS G2,
DATEADD(hour, 1, DATEADD(day, 27, DATEADD(month, 9, Y.THE_DATE))) AS H2,
DATEADD(hour, 0, DATEADD(day, 28, DATEADD(month, 9, Y.THE_DATE))) AS I2,
DATEADD(hour, 1, DATEADD(day, 28, DATEADD(month, 9, Y.THE_DATE))) AS J2,
DATEADD(hour, 0, DATEADD(day, 29, DATEADD(month, 9, Y.THE_DATE))) AS K2,
DATEADD(hour, 1, DATEADD(day, 29, DATEADD(month, 9, Y.THE_DATE))) AS L2,
DATEADD(hour, 0, DATEADD(day, 30, DATEADD(month, 9, Y.THE_DATE))) AS M2,
DATEADD(hour, 1, DATEADD(day, 30, DATEADD(month, 9, Y.THE_DATE))) AS N2
) AS Z
CROSS APPLY (
SELECT DR.STARTING_DATE, DR.ENDING_DATE
FROM DATE_RANGES AS DR
WHERE DR.THE_DATE = Y.THE_DATE
) AS R
ORDER BY Y.THE_DATE;
The results are in the attached spreadsheet.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 14, 2018 at 4:07 am
Here's what I ended up with
/****** Object: UserDefinedFunction [dbo].[UTC_LocalTime] Script Date: 14/06/2018 10:27:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Dave Jackson
-- Create date: 14 June 2018
-- Description: Parses a DATETIME value and adds 1 hour if it is BST
-- =============================================
ALTER FUNCTION [dbo].[UTC_LocalTime]
(
@Date DATETIME
)
RETURNS TABLE
AS
RETURN
(
SELECT @Date AS RecordedTime, CASE
WHEN DATETIMEFROMPARTS(
YEAR(@Date),
MONTH(@Date),
DATEPART(DAY, @Date),
DATEPART(HOUR, @Date),
DATEPART(MINUTE, @Date),
DATEPART(SECOND, @Date),
DATEPART(ms, @Date)) BETWEEN DATETIMEFROMPARTS(
YEAR(@Date),
3,
DATEPART(
DAY,
DATEADD(
DAY,
DATEDIFF(
DAY,
'19000107',
DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 30)) / 7 * 7,
'19000107')),
1,
0,
0,
0) AND DATETIMEFROMPARTS(
YEAR(@Date),
10,
DATEPART(
DAY,
DATEADD(
DAY,
DATEDIFF(
DAY,
'19000107',
DATEADD(
MONTH,
DATEDIFF(MONTH, 0, @Date),
30)) / 7 * 7,
'19000107')),
0,
59,
59,
997) THEN DATEADD(HOUR, 1, @Date)
ELSE @Date END AS GMTBST_Time
)
And here is a test harness
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
SELECT RandomDate
FROM #testEnvironment
SELECT utc.RecordedTime, utc.GMTBST_Time
FROM #testEnvironment
CROSS APPLY dbo.UTC_LocalTime(RandomDate) utc
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
It replaces a function call that takes 18 seconds on this data set. This runs in 4! 😀
Thanks for your help
Dave
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply