Calculation of Bi-Week Date using ANSI SQL (supported by Universe Designer)

[Former Member]

Hello Everyone,

I have a calendar table where I need to add an additional column (BIWEEKENDDATE), which is the last column in exmple. In Calendar table serial date is used to calculate the year, month, day and weekend however I would like to have one more column with BIWEEKENDDATE. I have already added the column to show what I want....

Example:

SDATE	        YEAR	MONTH	DAY	WEEKENDDATE	       BIWEEKENDDATE
1/1/2011	2011	1	1	1/1/2011		1/1/2011
1/2/2011	2011	1	2	1/8/2011		1/15/2011
1/3/2011	2011	1	3	1/8/2011		1/15/2011
1/4/2011	2011	1	4	1/8/2011		1/15/2011
1/5/2011	2011	1	5	1/8/2011		1/15/2011
1/6/2011	2011	1	6	1/8/2011		1/15/2011
1/7/2011	2011	1	7	1/8/2011		1/15/2011
1/8/2011	2011	1	8	1/8/2011		1/15/2011
1/9/2011	2011	1	9	1/15/2011		1/15/2011
1/10/2011	2011	1	10	1/15/2011		1/15/2011
1/11/2011	2011	1	11	1/15/2011		1/15/2011
1/12/2011	2011	1	12	1/15/2011		1/15/2011
1/13/2011	2011	1	13	1/15/2011		1/15/2011
1/14/2011	2011	1	14	1/15/2011		1/15/2011
1/15/2011	2011	1	15	1/15/2011		1/15/2011
...
...					
1/21/2011	2011	1	21	1/22/2011		1/29/2011

Thanks in advance

[former_member142702 · Accepted solution]

WHich database do you use?

I do believe you can calculate this using a formula (THIS IS PSEUDO CODE) like

DATEADD(day,ceil((Week(sdate))/2)27,'1-1-2011')

But I am not really sure if you can implement this with pure ANSI SQL.

Note this works because 1-1-2011 was a Saturday. Otherwise you have to find a way to get the last Saturday of last year.

Regards,

Stratos

[Former Member · Accepted solution]

Thanks Stratos, the logic helped to meet the requirement. I used another formula using same logic for two reasons 1) I want to use the forumla for more than one year 2) The WEEK number function does not support the purpose whenever year ends in the middle of week.

NEW Formula: Starting from March/9/2002 to 9999/1/1 (db value in Calendar table)

SELECT CASE MOD(DATE('MAR 9 2002') - SDATE , 14)

WHEN 0 then SDATE ELSE SDATE -(MOD(SDATE-DATE('MAR 9 2002') , 14)) + 14 END BIWEEKENDDATE

FROM CALENDAR

WHERE SDATE BETWEEN '03-09-2002' AND '01-01-9999'

Thanks

AK