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

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

Dec 01, 2017 at 12:17 AM
0

Hi Kiran,

Any luck?? we are facing the same issue.

Show 1 Share

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')
)
+
+
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```