UDF to Convert CET date into UTC

• npranj

  SSCrazy

  Points: 2323

  More actions

  February 15, 2010 at 5:39 am

  #216648

  Hi,

  I need to write a UDF to convert CET date into UTC.

  I have written a UDF to convert CET date into UTC. However, it fails in a few scnarios.

  The UDF code -

  CREATE FUNCTION [dbo].[udf_ConvertfromCETtoUTC] (@CETDate AS DATETIME)

  RETURNS DATETIME

  WITH SCHEMABINDING

  AS

  BEGIN

  DECLARE @v_start_of_dst DATETIME

  DECLARE @v_end_of_dst DATETIME

  DECLARE @MarchDate DATETIME

  DECLARE @OctoberDate DATETIME

  DECLARE @MarchLastSunday DATETIME

  DECLARE @OctoberLastSunday DATETIME

  DECLARE @UTCDate DATETIME

  DECLARE @Weekday INT

  SET @MarchDate=Convert(DATETIME, '24-Mar-' + cast( YEAR(@CETDate) AS VARCHAR(5)), 113)

  SELECT @Weekday=DATEPART(WEEKDAY,@MarchDate)

  IF @Weekday=1

  SELECT @MarchLastSunday=DATEADD(d,7,@MarchDate)

  ELSE IF @Weekday=2

  SELECT @MarchLastSunday=DATEADD(d,6,@MarchDate)

  ELSE IF @Weekday=3

  SELECT @MarchLastSunday=DATEADD(d,5,@MarchDate)

  ELSE IF @Weekday=4

  SELECT @MarchLastSunday=DATEADD(d,4,@MarchDate)

  ELSE IF @Weekday=5

  SELECT @MarchLastSunday=DATEADD(d,3,@MarchDate)

  ELSE IF @Weekday=6

  SELECT @MarchLastSunday=DATEADD(d,2,@MarchDate)

  ELSE

  SELECT @MarchLastSunday=DATEADD(d,1,@MarchDate)

  SET @OctoberDate=Convert(DATETIME, '24-Oct-' + cast( YEAR(@CETDate) AS VARCHAR(5)), 113)

  SELECT @Weekday=DATEPART(WEEKDAY,@OctoberDate)

  IF @Weekday=1

  SELECT @OctoberLastSunday=DATEADD(d,7,@OctoberDate)

  ELSE IF @Weekday=2

  SELECT @OctoberLastSunday=DATEADD(d,6,@OctoberDate)

  ELSE IF @Weekday=3

  SELECT @OctoberLastSunday=DATEADD(d,5,@OctoberDate)

  ELSE IF @Weekday=4

  SELECT @OctoberLastSunday=DATEADD(d,4,@OctoberDate)

  ELSE IF @Weekday=5

  SELECT @OctoberLastSunday=DATEADD(d,3,@OctoberDate)

  ELSE IF @Weekday=6

  SELECT @OctoberLastSunday=DATEADD(d,2,@OctoberDate)

  ELSE

  SELECT @OctoberLastSunday=DATEADD(d,1,@OctoberDate)

  SET @v_start_of_dst = @MarchLastSunday

  SET @v_end_of_dst =@OctoberLastSunday

  IF @CETDate <DATEADD(HH,1,@v_start_of_dst)

  -- Before the March switch time all times are CET.

  SET @UTCDate = DATEADD(HH,-1,@CETDate)

  ELSE IF @CETDate <DATEADD(HH,1,@v_end_of_dst)

  -- Between the March and October switches, all times are CEST

  SET @UTCDate = DATEADD(HH,-2,@CETDate)

  ELSE

  -- After the October switch all times are CET

  SET @UTCDate =DATEADD(HH,-1,@CETDate)

  RETURN @UTCDate

  END

  --- to test this..

  --CET to UTC

  select [dbo].[udfn_ConvertfromCETtoUTC] ('1-jan-2010') --

  select [dbo].[udfn_ConvertfromCETtoUTC] ('1-jun-2010 02:00') --

  select [dbo].[udfn_ConvertfromCETtoUTC] ('26-oct-2010 02:00') --

  select [dbo].[udfn_ConvertfromCETtoUTC] ('31-oct-2010 02:00') --

  select [dbo].[udfn_ConvertfromCETtoUTC] ('31-oct-2010 03:00') --

  select [dbo].[udfn_ConvertfromCETtoUTC] ('31-oct-2010 01:30') --

  select [dbo].[udfn_ConvertfromCETtoUTC] ('31-oct-2010 00:30') --

  select [dbo].[udfn_ConvertfromCETtoUTC] ('31-oct-2010 01:00') --

  select [dbo].[udfn_ConvertfromCETtoUTC] ('31-oct-2010 00:00') --

  select [dbo].[udfn_ConvertfromCETtoUTC] ('28-mar-2010 01:00') -- fail

  select [dbo].[udfn_ConvertfromCETtoUTC] ('28-mar-2010 01:30') --

  select [dbo].[udfn_ConvertfromCETtoUTC] ('28-mar-2010 01:00') --

  select [dbo].[udfn_ConvertfromCETtoUTC] ('28-mar-2010 02:00') -- fail

  select [dbo].[udfn_ConvertfromCETtoUTC] ('28-mar-2010 03:00') -- pass

  I am using the url -

  http://www.timeanddate.com/worldclock/converter.html

  to test the conversions.

• ohack

  SSCrazy

  Points: 2043

  More actions

  February 16, 2010 at 5:51 am

  #1119209

  Hi,

  There's actually nothing much wrong with your code logic-wise - there really are some grey areas as can be proved by putting 28th March 2010 2AM into the webpage you mentioned. The fact is that in a time system that uses periodical time shifts there will be times that just don't exist or perhaps in the case of clocks going back even happen twice! All you can do is settle on some rules and implement them.

  I would have significantly abbreviated the code though, and SCHEMABINDING does not seem relevant here as there are no other database objects referenced to bind to (see BOL):

  ALTER FUNCTION [dbo].[udf_ConvertfromCETtoUTC] (@CETDate AS DATETIME)

  RETURNS DATETIME

  AS

  BEGIN

  DECLARE @DstStart datetime

  DECLARE @DstEnd datetime

  DECLARE @UtcDate datetime

  SELECT @DstStart = DATEADD(hour, 1,DATEADD(day, DATEDIFF(day, 0, '31/Mar' + CAST(YEAR(@CETDate) AS varchar)) -

  (DATEDIFF(day, 6, '31/Mar' + CAST(YEAR(@CETDate) AS varchar)) % 7), 0)),

  @DstEnd = DATEADD(hour, 1,DATEADD(day, DATEDIFF(day, 0, '31/Oct' + CAST(YEAR(@CETDate) AS varchar)) -

  (DATEDIFF(day, 6, '31/Oct' + CAST(YEAR(@CETDate) AS varchar)) % 7), 0))

  SELECT @UtcDate = CASE WHEN @CETDate <= @DstEnd AND @CETDate >= @DstStart

  THEN DATEADD(hour, -2, @CETDate)

  ELSE DATEADD(hour, -1, @CETDate) END

  RETURN @UtcDate

  END