T-SQL 2008 R2 Australia Daylight Saving Time [SSRS Report] ?

Question

T-SQL 2008 R2 Australia Daylight Saving Time [SSRS Report] ?

Hi, I have SSRS 2008 R2 report that uses T-SQL 2008 R2 with two report parameters :

@Date_From -> YYYY-MM-DD format
@Date_To -> YYYY-MM-DD format

Below query returns data in daylight saving time (Australia Melbourne, Victoria Time where Daylight start first Sunday in October and end on first Sunday in April) :

Order_Date                        Supplier      Qty
2012-08-25 13:00:00           SUP1         20.00   (Outside Daylight)
2012-08-25 14:00:00           SUP1         12.00    (Outside Daylight)
2012-08-25 15:00:00           SUP1         89.00    (Outside Daylight)
2012-10-08 13:00:00           SUP1         11.00    (In Daylight)
2012-10-08 14:00:00           SUP1         18.00    (In Daylight)
2012-10-08 15:00:00           SUP1         25.00    (In Daylight)
.........
.........
.........

For example if user passes report parameter @Date_From = '2012-08-20' and @Date_To = '2012-11-25' then final query result I'm looking for is :
Order_Date                        Supplier      Qty
2012-08-25 14:00:00           SUP1         20.00    (Outside Daylight - Add an hour if date is not in between Daylight saving ????)
2012-08-25 15:00:00           SUP1         12.00    (Outside Daylight - Add an hour if date is not in between Daylight saving ????)
2012-08-25 16:00:00           SUP1         89.00    (Outside Daylight - Add an hour if date is not in between Daylight saving ????)
2012-10-08 12:00:00           SUP1         11.00    (In Daylight - Subtract an hour if date is in Daylight saving ????)
2012-10-08 13:00:00           SUP1         18.00    (In Daylight - Subtract an hour if date is in Daylight saving ????)
2012-10-08 14:00:00           SUP1         25.00    (In Daylight - Subtract an hour if date is in Daylight saving ????)
.........
.........
.........

Any idea? Thanks.

Answer 1

Hi

    You can create user function like below and use it in your SELECT

CREATE FUNCTION [dbo].[GetInOutDaylight](@date datetime)
RETURNS datetime
AS
BEGIN
	DECLARE @year int = DATEPART(year, @date)

	DECLARE @dateStartDaylight datetime
	DECLARE @dataEndDaylight datetime

	SET @dateStartDaylight = CAST(CAST(@year AS nvarchar) + '-10-'+ CAST((7 - (floor(@year * 5 / 4) + 5) % 7) AS nvarchar)+' 00:00:00' AS datetime)
	SET @dataEndDaylight = CAST(CAST(@year AS nvarchar) + '-04-'+ CAST((7 - (floor(@year * 5 / 4) + 4) % 7) AS nvarchar)+' 00:00:00' AS datetime)

	DECLARE @changeVal int

	SET @changeVal = CASE WHEN @date BETWEEN @dataEndDaylight AND @dateStartDaylight THEN 1 ELSE -1 END

	RETURN DATEADD(HOUR, @changeVal, @date)	
END

---

everything is a matter of probability...