Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Dec 01, 2017 at 12:17 AM

    Hi Kiran,

    Any luck?? we are facing the same issue.

    Add comment
    10|10000 characters needed 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