Search code examples
ssislistenergetdate

GETDATE() and Multiple Servers connecting via a listener

We have 3 servers London, Amsterdam, Frankfurt. Connect via a listener. so using GetDate() to populate tables etc will give a different result depending on if connection to London or not. What is the easiest way to always get the London time.

Thanks Geoff

Solution

  • Easiest to use GETUTCDATE() instead. The datetimeoffset returned is unambiguous. To get a UK time, convert using AT TIME ZONE, then convert to datetime or datetime2:

    SELECT CONVERT(datetime2(0),CONVERT(datetimeoffset,GETUTCDATE()) AT TIME ZONE 'GMT 
Standard Time')

    Note that timezone 'Greenwich Standard Time' returns GMT, which is not UK time in summer. Timezone 'GMT Standard Time' reflects UK time in both summer and winter - which is not GMT, but GMT+1 in summer. This doesn't seem to be documented, and the names are not helpful!