How to use Week function in HANA but with different day as Start of the Week?

[former_member308471]

Hi All,

We have the WEEK Function in HANA and it computes the week number.

Can we know which Day of the week it takes as reference as start_of_the_week

But is there a way where we can pass or set a parameter for the start_day_of_the_week?

For example consider the date 2nd Jan 2017

select WEEK(TO_DATE('20170102','YYYYMMDD')) from dummy

2

if we consider Monday as the "start_of_the_week" then the output is 2 also its the same when we run in HANA SQL.

but if Sunday is the "start_of_the_week" then the output expected is 1.

As per business needs the start_of_the_week needs to be "Sunday" or any other day,

Is there a way to achieve this using raw SQL?

Regards,

Kiran Shenvi

Regards,

Kiran

[former_member375266]

Hi Kiran,

Any luck?? we are facing the same issue.

[former_member308471]

Hi Santosh,

This is sample code :

--Sunday AS START DATE
select 
FLOOR(
(
DAYS_BETWEEN
(
TO_DATE(EXTRACT(YEAR FROM TO_DATE('20170101','YYYYMMDD'))||'0101','YYYYMMDD'),
TO_DATE('20170101','YYYYMMDD')
)
+
WEEKDAY(ADD_DAYS(TO_DATE(EXTRACT(YEAR FROM TO_DATE('20170101','YYYYMMDD'))||'0101','YYYYMMDD'),+1))
+
7
)/7
)
from DUMMY;
-- o/p -> 1

This is the manual formula to compute week day with diff day as the start of the week.

Note the range changing from +1, +0, -1, -2, -3, -4, -5

Div(date-YearStart(date) + WeekDay(YearStart(date)+1) +7,7) - Sunday
Div(date-YearStart(date) + WeekDay(YearStart(date)+0) +7,7) - Monday
Div(date-YearStart(date) + WeekDay(YearStart(date)-1) +7,7) - Tuesday
Div(date-YearStart(date) + WeekDay(YearStart(date)-2) +7,7) - Wednesday
Div(date-YearStart(date) + WeekDay(YearStart(date)-3) +7,7) - Thursday
Div(date-YearStart(date) + WeekDay(YearStart(date)-4) +7,7) - Friday
Div(date-YearStart(date) + WeekDay(YearStart(date)-5) +7,7) - Saturday

[sreehari_vpillai]

^^ painful idea , but no other options.