CODEWITHSUNDEEP
sqlsql-server
Steve
Steve

Reputation: 4691

Sql Server Specify time in another timezone

I want to write a query to only return results if it is after 2pm in the UK. I also know that EST to London time is a five hour offset currently. How can I do this?

select *
from table
where blah blah
and 2pm UK time < GETDATE()

Upvotes: 2

Views: 1696

Answers (2)

Siyual
Siyual

Reputation: 16917

Since the UK timezone can be affected by Daylight Savings Time, I'd suggest building a DST table and using that to create the date.

This article was referenced for this solution: http://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server--part-1/

Create table:

CREATE TABLE dbo.TZCalendar
(
  [Year]        Int PRIMARY KEY,
  UTC_DST_Start SMALLDATETIME NOT NULL,
  UTC_DST_End   SMALLDATETIME NOT NULL,
  ET_DST_Start  AS CONVERT(SMALLDATETIME,DATEADD(HOUR, -4, UTC_DST_Start)),
  ET_DST_End    AS CONVERT(SMALLDATETIME,DATEADD(HOUR, -5, UTC_DST_End))
);

SET DATEFIRST 7;
;WITH cte(d,p) AS 
(
  -- all the years from 2000 through 50 years after the current year:
  SELECT TOP (YEAR(GETDATE())-2000+51) DATEADD(YEAR,number,'20000101'),
    CASE WHEN number < 7 THEN 1 ELSE 0 END -- year < 2007 = 1, else 0
    FROM [master].dbo.spt_values WHERE [type] = N'P' ORDER BY number
)
INSERT dbo.TZCalendar([Year],UTC_DST_Start,UTC_DST_End)
SELECT Year(d),
 -- First Sunday in April (< 2007) or second Sunday in March (>= 2007):
 DATEADD(HOUR, 7, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,2+p,d))+1)%7
    +(7*ABS(p-1)),DATEADD(MONTH,2+p,d))),
 -- Last Sunday in October (< 2007) or first Sunday in November (>= 2007):
 DATEADD(HOUR, 6, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,10,d))+1)%7
    -(7*p),DATEADD(MONTH,10,d)))
FROM cte
ORDER BY d;

Next, I would create a function to pull the current UK time:

Create Function dbo.fnGetUKTime()
Returns DateTime
As Begin
    Declare @London DateTime = GetUTCDate()

    Declare @Offset Int = 0

    Select  @Offset = Case When @London Between UTC_DST_Start And UTC_DST_End Then 1 Else 0 End
    From    dbo.TZCalendar
    Where   Year = Year(@London)

    Set @London = DateAdd(Hour, @Offset, @London)

    Return @London
End
Go

Then, you can reference this in your query:

select *
from table
where blah blah
and DatePart(Hour, dbo.fnGetUKTime()) > 14

Upvotes: 2

Ed B
Ed B

Reputation: 796

You can use datepart to determine the current datetime offset (in minutes) of your server:

select datepart(tz, sysdatetimeoffset())

For example, in the UK at the moment (during BST), this gives 60.

To find out whether you are before or after 2 pm in another timezone, you need to compare your current time to 2 pm, plus your offset, minus the offset in the other timezone, so for example, 2 pm UK time in your current time zone is:

select dateadd(minute, -<current uk offset>, dateadd(minute, datepart(tz, sysdatetimeoffset()), cast('14:00' as time)))

Note that the current offset of the timezone in the UK is 60 in summer, and 0 in winter. If you're only interested in GMT, rather than BST, you can omit this part of the calculation.

Upvotes: 2

Related Questions