Axapta/DynamicsAx: UTC datetime conversion

Question

We are trying to interpret the data stored in Axapata's TIMEZONESRULESDATA table. Particularly, we'd like to figure out how it stores DST begin/end times. So far, my guess is:

TZENUM: foreign key referencing TIMEZONESLIST (time zone name and identifier)
YEAR: 0 if rule is valid indefinitely or a year where the timezone rule is in effect
BIAS: offset to UTC time in minutes
DBIAS: offset of DST (added to BIAS to get total offset from UTC)

Now for the part I don't understand:

DYEAR,DMONTH,DDAYOFWEEK,DDAY,DHOUR,DMINUTE,DSECOND
SYEAR,SMONTH,SDAYOFWEEK,SDAY,SHOUR,SMINUTE,SSECOND

I would guess from the dates that the D* is the start date of DST and S* is end. However, I do not understand why it would need a year and a dayofweek field. Also, *DAY does not seem to indicate the day of month - at least it doesn't indicate the correct one for switching DST. It also doesn't seem to correspond to the dates at which Axapta's own DateTimeUtil or Form autoconversion converts the dates.

Does anyone know how to interprete this table? Or where I could look it up?

Sören

Answer 1

DDAY and SDAY represents week of month (1-4, 5 = last week)

DDAYOFWEEK and SDAYOFWEEK represents day of week (0 = Sunday)

How will you use this information?

Answer 2

DDAY and SDAY looked like they indicate the week of month, but then we found DDAY- and SDAY-values of 6, 23 or 28. Are these the weeks of the year, or were the values > 5 hijacked for individual days?

My theory was this: with DMONTH = 8 and DDAYOFWEEK = 6, we have

• DDAY = 4 ==> 4th saturday in August
• DDAY = 5 ==> last saturday in August
• DDAY = n > 5 ==> n-th of August (possible clash with DDAYOFWEEK?)

So what are SDAYs and DDAYs that are larger than 5?

Answer 3

I have made up two SQL functions to convert Dynamics AX UTC dates to the appropriate time zone date/time from TIMEZONESRULESDATA table. They appear to work in the scenarios I've tested but I'd be pleased to get feedback.

The first function is fed the UTC DateTime and TZID from any AX table and generates the Date/Time in the time zone with any daylight savings adjustment:

-- *** IMPORTANT NOTE: @@DATEFIRST must be 7 for this to work ***
CREATE FUNCTION [dbo].[ConvertUTCDateTime] (@DateTime DATETIME, @TZID INT)
RETURNS DATETIME
AS
BEGIN

DECLARE @AdjustedDateTime DATETIME
SET @AdjustedDateTime=@DateTime

-- Fields to be extracted from TIMEZONESRULESDATA record for TZID
DECLARE @Bias INT, @DBias INT
DECLARE @DMonth INT, @DDayOfWeek INT, @DDay INT, @DHour INT, @DMinute INT, @DSecond INT     -- Start of Daylight Saving
DECLARE @SMonth INT, @SDayOfWeek INT, @SDay INT, @SHour INT, @SMinute INT, @SSecond INT     -- End of Daylight Saving
-- Daylight Saving Date/Time ranges
DECLARE @DSTFromDateTime1 DATETIME, @DSTToDateTime1 DATETIME, @DSTFromDateTime2 DATETIME, @DSTToDateTime2 DATETIME

SELECT
    @Bias=tzr.BIAS,@DBias=tzr.DBIAS,
    @DMonth=tzr.DMONTH, @DDayOfWeek=tzr.DDAYOFWEEK, @DDay=tzr.DDAY, @DHour=tzr.DHOUR, @DMinute=tzr.DMINUTE, @DSecond=tzr.DSECOND,
    @SMonth=tzr.SMONTH, @SDayOfWeek=tzr.SDAYOFWEEK, @SDay=tzr.SDAY, @SHour=tzr.SHOUR, @SMinute=tzr.SMINUTE, @SSecond=tzr.SSECOND
FROM MyAXDatabase..TIMEZONESRULESDATA tzr
WHERE tzr.RULEID=@TZID

IF @Bias IS NOT NULL
    BEGIN
        SET @AdjustedDateTime=DATEADD(MINUTE, (-1)*@Bias, @DateTime )       -- Standard Time Zone Adjustment from UTC
        IF @DMonth>0    -- If there is Daylight Saving
            BEGIN
                SET @DSTFromDateTime1=dbo.GetDSTDateTime(@AdjustedDateTime, @DMonth, @DDayOfWeek, @DDay, @DHour, @DMinute, @DSecond )   -- Get DS Start date in year
                SET @DSTToDateTime2=dbo.GetDSTDateTime(@AdjustedDateTime, @SMonth, @SDayOfWeek, @SDay, @SHour, @SMinute, @SSecond ) -- Get DS End date in year
                IF @DSTFromDateTime1>@DSTToDateTime2
                    BEGIN
                        SET @DSTToDateTime1= DATEADD(SECOND, -1, CAST(DATEFROMPARTS( YEAR(@AdjustedDateTime)+1, 1, 1) as DATETIME)) -- End of Current Year
                        SET @DSTFromDateTime2= DATEFROMPARTS( YEAR(@AdjustedDateTime), 1, 1)                        -- Start of Current Year
                    END
                ELSE
                    BEGIN
                        SET @DSTToDateTime1=@DSTToDateTime2
                        SET @DSTFromDateTime2=@DSTFromDateTime1
                    END
                IF @AdjustedDateTime BETWEEN @DSTFromDateTime1 AND @DSTToDateTime1 
                    OR @AdjustedDateTime BETWEEN @DSTFromDateTime2 AND @DSTToDateTime2
                    SET @AdjustedDateTime=DATEADD(MINUTE, (-1)*@DBias, @AdjustedDateTime )  -- Make Daylight Saving adjustment if in DST date range
            END
    END

RETURN @AdjustedDateTime

END

The second function works out the daylight savings start or end date based on parameters fed from the TIMEZONESRULESDATA fields:

-- *** IMPORTANT NOTE: @@DATEFIRST must be 7 for this to work ***
CREATE FUNCTION [dbo].[GetDSTDateTime](
@DateTime DATETIME,     -- Base Date
@Month INT,         -- Month for Start/End of DST
@DayOfWeek INT,         -- Day of Week  0=Sun..6=Sat (based on coding in TIMEZONESRULESDATA table)
@Day INT,           -- Week of the Month (confusing?!) 1-5 ; 5 means last week
@Hour INT, 
@Minute INT, 
@Second INT
) RETURNS DATETIME
AS
BEGIN

DECLARE @MyDateTime DATETIME

SET @MyDateTime=DATEFROMPARTS( YEAR(@DateTime), @Month, 1) -- First day of DST Start/End Month from @BaseDate year
SET @DayOfWeek=@DayOfWeek + 1   -- Adjust to tie in with SQL DoW 1-7

-- Establish first selected DayOfWeek in the month
IF @DayOfWeek >= DATEPART(WEEKDAY, @MyDateTime )
    SET @MyDateTime=DATEADD(DAY, @DayOfWeek - DATEPART(WEEKDAY, @MyDateTime), @MyDateTime)
ELSE
    SET @MyDateTime=DATEADD(DAY, 7-(DATEPART(WEEKDAY, @MyDateTime) - @DayOfWeek), @MyDateTime)

-- Add the appropriate number of weeks
SET @MyDateTime=DATEADD(DAY, 7*(@Day-1), @MyDateTime)

-- For last week of month ensure that date is in correct month
WHILE MONTH(@MyDateTime)<>@Month
    BEGIN
        SET @MyDateTime=DATEADD(DAY, -7, @MyDateTime)
    END;

-- Add on Hours, Minutes and Seconds
SET @MyDateTime=DATEADD(SECOND, @Second, DATEADD(MINUTE, @Minute, DATEADD(HOUR, @Hour, @MyDateTime)))

RETURN @MyDateTime

END