Consider improving dialect translation of DATENAME(week, date, monday)

The following Tableau TDVT test case fails using the Actian JDBC and ODBC connector code due to not matching any of the sets of expected values as defined in datename.sow.week, datename.sow.week.1, datename.sow.week.2.

Test Case: DATENAME('week', [date2], 'monday') Actual: 1,11,16,17,21,23,28,29,32,36,37,44,5,6 Expected: 2,11,17,18,22,23,29,30,33,36,37,38,45,5,7 _(Actual & Expected values from a typical TDVT report: test_resultscombined.csv)

From dialect.tdd, the function <date-function name='DATENAME' return-type='str'> is defined with 5 different implementations, having these formulas for calculating the "week" part of the date:

<formula part='week'> CAST(CAST(WEEK(%2,0)    AS INTEGER) AS VARCHAR(3)) </formula>
<formula part='week'> CAST(CAST(WEEK(%2,0) +1 AS INTEGER) AS VARCHAR(6)) </formula>
<formula part='week'> CAST(CAST(WEEK(%2,0)    AS INTEGER) AS VARCHAR(4)) </formula>
<formula part='week'> CAST(CAST(WEEK(%2,0)    AS INTEGER) AS VARCHAR(5)) </formula>
<formula part='week'> CAST(Cast(WEEK(%2,0)    AS INTEGER) AS VARCHAR(3)) </formula>

Reference calculations and expected values for "week" of the Calcs.date2 column using various methods

Date2	Connectors JDBC & ODBC	PlanetCalc	Query A	Query B	Query C	TDVT Expected week, week.1, week.2
1988-01-05	1	2	1	1	1	2	2
1974-03-17	11	11	11	11	11	11	11
1977-04-20	16	16	16	16	16	17	17
1994-04-20	16	16	16	16	16	17	17
2002-04-27	16	17	16	17	17	17	17
1974-05-03	17	18	17	18	18	18	18
1997-05-30	21	22	21	22	22	22	22
1995-06-04	23	22	23	22	22	23	23
1972-07-12	28	28	28	28	28	29	29
1980-07-26	29	30	29	30	30	30	30
1998-08-12	32	33	32	33	33	33	33
1995-09-03	36	35	36	35	35	36	36
1976-09-09	36	37	36	37	37	37	37
1997-09-19	37	38	37	38	38	38	38
1980-11-07	44	45	44	45	45	45	45
2001-02-04	5	5	5	5	5	5	5
1977-02-08	6	6	6	6	6	7	7

Queries for the values in the above table

A : SELECT CAST(Cast(WEEK(date2,0) AS INTEGER) AS VARCHAR(3)) AS WEEK FROM Calcs Calcs order by 1
B : SELECT CAST(Cast(WEEK(date2,1) AS INTEGER) AS VARCHAR(3)) AS WEEK FROM Calcs Calcs order by 1
C : SELECT CAST(Cast(WEEK_ISO(date2) AS INTEGER) AS VARCHAR(3)) AS WEEK FROM Calcs Calcs order by 1

Summary

The purpose of including the above table with data is to provide detailed data to help decide which calculation method aligns best with the true results for test case DATENAME('week', [date2], 'monday'). It appears that queries B and C are probably most accurate according to results from an arbitrary web site called PlanetCalc for calculating week of the year for given dates.

[clach04@usau-engfs01 ~]$ cal 1988
                   1988

       January               February                 March
Su Mo Tu We Th Fr Sa   Su Mo Tu We Th Fr Sa   Su Mo Tu We Th Fr Sa
        1  2       1  2  3  4  5  6          1  2  3  4  5
 3  4  5  6  7  8  9    7  8  9 10 11 12 13    6  7  8  9 10 11 12
10 11 12 13 14 15 16   14 15 16 17 18 19 20   13 14 15 16 17 18 19
17 18 19 20 21 22 23   21 22 23 24 25 26 27   20 21 22 23 24 25 26
24 25 26 27 28 29 30   28 29                  27 28 29 30 31
31
    April                   May                   June
Su Mo Tu We Th Fr Sa   Su Mo Tu We Th Fr Sa   Su Mo Tu We Th Fr Sa
        1  2    1  2  3  4  5  6  7             1  2  3  4
 3  4  5  6  7  8  9    8  9 10 11 12 13 14    5  6  7  8  9 10 11
10 11 12 13 14 15 16   15 16 17 18 19 20 21   12 13 14 15 16 17 18
17 18 19 20 21 22 23   22 23 24 25 26 27 28   19 20 21 22 23 24 25
24 25 26 27 28 29 30   29 30 31               26 27 28 29 30

    July                  August                September
Su Mo Tu We Th Fr Sa   Su Mo Tu We Th Fr Sa   Su Mo Tu We Th Fr Sa
        1  2       1  2  3  4  5  6                1  2  3
 3  4  5  6  7  8  9    7  8  9 10 11 12 13    4  5  6  7  8  9 10
10 11 12 13 14 15 16   14 15 16 17 18 19 20   11 12 13 14 15 16 17
17 18 19 20 21 22 23   21 22 23 24 25 26 27   18 19 20 21 22 23 24
24 25 26 27 28 29 30   28 29 30 31            25 26 27 28 29 30
31
       October               November               December
Su Mo Tu We Th Fr Sa   Su Mo Tu We Th Fr Sa   Su Mo Tu We Th Fr Sa
           1          1  2  3  4  5                1  2  3
 2  3  4  5  6  7  8    6  7  8  9 10 11 12    4  5  6  7  8  9 10
 9 10 11 12 13 14 15   13 14 15 16 17 18 19   11 12 13 14 15 16 17
16 17 18 19 20 21 22   20 21 22 23 24 25 26   18 19 20 21 22 23 24
23 24 25 26 27 28 29   27 28 29 30            25 26 27 28 29 30 31
30 31

The ISO 8601 rule is: The first week of the year is the week containing the first Thursday. So if January 1 falls on a Friday, it belongs to the last week of the previous year. If December 31 falls on a Wednesday, it belongs to week 01 of the following year.

from https://myweb.ecu.edu/mccartyr/aboutwdc.htm and also https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-weeknum

ActianCorp / actian_tableau_connector

Consider improving dialect translation of DATENAME(week, date, monday) #31

Reference calculations and expected values for "week" of the Calcs.date2 column using various methods

Summary