How to determine of Week of the month in HANA SQL?

[Former Member]

Hi,

Please can you tell me how can i determine the week for a particular month,starting from Sunday.

I have written script in HANA as :

SELECT WEEK(TO_DATE ('2013-03-01', 'YYYY-MM-DD'))

- WEEK(ADD_DAYS(TO_DATE ('2013-03-01', 'YYYY-MM-DD'),-(DAYOFMONTH(TO_DATE ('2013-03-01', 'YYYY-MM-DD'))-1)))+1 FROM DUMMY;

But it doesn't seem accurate, it fails for date 2013-03-31, as the week number should be 6.

in SQL similar functionality is achieved by (for week starting from Monday):

SELECT WEEK(my_date_field,5) - WEEK(DATE_SUB(my_date_field, INTERVAL DAYOFMONTH(my_date_field)-1 DAY),5)+1

link for SQL code : http://think-robot.com/2009/04/week-of-the-month-in-mysql/

Please help!

Regards

Sushant

[former_member182114 · Accepted solution]

Hi Sushant,

You can use option "Generate Time Data" found on Help -> Quick Launch view

Choose: Gregorian type, Year from and to, Granularity (day), First day of the week = SUNDAY

This will populate table _SYS_BI.M_TIME_DIMENSION

Link your table with this one joining with Date (see on table that you have type DATE_SAP or DATE_SQL) and look for field WEEK.

Regards, Fernando Da Rós

[Former Member]

Hi Sushanth ,

i'm also getting the same error while executing the PAL Algorithm.

could not execute 'CALL palLR(MLR_DATA_TAB, "#MLR_CONTROL_TAB", MLR_RESULTS_TAB, MLR_FITTED_TAB, MLR_SIGNIFICANCE_TAB, ...' in 379 ms 845 µs .

SAP DBTech JDBC: [2048]: column store error: search table error: [2620] executor: plan operation failed;required data for execution not found .

Can you please suggest me the solution.

[lbreddemann]

Hi Sushant,

for different timezone support (which is basically, what you're asking here), please check SAP note https://service.sap.com/sap/support/notes/1791342.

- Lars

[former_member184768]

Hi Lars,

I think the note is not released externally yet. I get an error message like

The requested SAP Note is either in reworking or is released internally only

Regards,

Ravi

[Former Member]

Hi Ravindra,

I have a requirement which is explained below. How i should get in hana sql.

Requirement -- 1) get the first day of week. 2) get the last day of week.

Example -- If today's date is 15/08/2013 THEN first day of week should be 11/08/2013

                  If today's date is 15/08/2013 THEN last day of week should be 17/08/2013

In Oracle we can achieve it by writing below query. But how i should get in hana sql. ? please reply . U can mail me to @ amitmbit@gmail.com

-First day of current week(sunday)

select TRUNC(SYSDATE, 'Day') from dual;

--Last day of current week(sunday)

select TRUNC(SYSDATE, 'Day')+6 from dual;

[former_member184768]

Hi Amit,

Ideally you should have opened a new discussion thread. You can use ADD_DAYS function to achieve your requirement.

select to_date('15-08-2013', 'dd-mm-yyyy'),

weekday(to_date('15-08-2013', 'dd-mm-yyyy')),

add_days(to_date('15-08-2013', 'dd-mm-yyyy'), ((weekday(to_date('15-08-2013', 'dd-mm-yyyy')) + 1) * -1)) as "WEEK_FIRST_DAY"

from dummy

Regards,

Ravi