Week Number in Teradata from Monday to Sunday

Question

In Teradata SYS_CALENDAR.CALENDAR for year 2018 , the WEEK_OF_YEAR is starting from Sunday to Saturday. But I wanted WEEK_OF_YEAR to start from Monday to Sunday as 1.

so 01-Jan-2018 to 07-Jan-2018 WEEK_OF_YEARwill be 1 : : and 31-Dec-2018 WEEK_OF_YEARwill be 53 (Once year ended, should agian start from 1) and 01-Jan-2019 to 06-Jan-2019 WEEK_OF_YEARwill be 1

Based on your rule the 1st sunday of a year is always in week 1? — dnoeth, Mar 21 '18 at 10:03
But then you might get a week 54 (leap year starting on Sunday, e.g. 2012)? — dnoeth, Mar 21 '18 at 10:38

dnoeth · Accepted Answer · 2018-03-21T17:51:28.510

3

This will return your expected result, week from Sunday to Saturday and 1st Sunday of a year is always in week 1:

((DayNumber_Of_Year(calendar_date, 'compatible') + (6-DayNumber_Of_Week(calendar_date, 'iso'))) / 7) + 1

Use this once to create/update your own calendar. For repeated use you better wrap this calculation in a SQL UDF.

edited Mar 21 '18 at 17:51

answered Mar 21 '18 at 10:57

dnoeth

59,503
4
39
56

Exactly what i was looking for. Thanks – Prasanna Nandakumar Mar 21 '18 at 11:05

Week Number in Teradata from Monday to Sunday

1 Answers1