Skip to Content
0

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

Feb 09, 2017 at 05:45 AM

654

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Rajaganapathi Rangdale Srinivasa Rao Dec 01, 2017 at 12:17 AM
0

Hi Kiran,

Any luck?? we are facing the same issue.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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
0