cancel
Showing results for 
Search instead for 
Did you mean: 

DATE declaration in SAP HANA Stored Procedure

former_member187794
Participant
0 Kudos

Team

Can we declare a date in HANA stored procedure? Our requirement is to loop on a date field By month&Year.

Any help, on looping thru the 3 years by month. would be great.

Regards/Giri

lbreddemann
Active Contributor
0 Kudos

I'm pretty sure that your requirement is to perform some logic per month within a three-year period, but not that you have to run a loop in order to do that.

Looping is generally not a great idea in a SQL database.

former_member187794
Participant
0 Kudos

thanks Everyone, for your inputs.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Giri,

You can try below for looping the date field. It will loop through all the dates in between today and 3 years back date.

BEGIN

DECLARE CURSOR cur_rows FOR

select "DATE_SQL" from "_SYS_BI"."M_TIME_DIMENSION"

where "DATE_SQL"<=current_date and "DATE_SQL">=add_months(current_date,-36);

FOR currentrow as cur_rows DO

// You can access the date field value inside the loop using currentrow."DATE_SQL"

END FOR;

END;

If you want to loop only on year and month data you can try below

BEGIN

DECLARE CURSOR cur_rows FOR

select * from (select concat("YEAR","MONTH")as "YEARMONTH" from"_SYS_BI"."M_TIME_DIMENSION_MONTH")

where "YEARMONTH" <= CONCAT(SUBSTRING(current_date,1,4),SUBSTRING(current_date,6,2)) and

"YEARMONTH" >= CONCAT(SUBSTRING(add_months(current_date,-36),1,4),SUBSTRING(add_months(current_date,-36),6,2));

FOR currentrow as cur_rows DO

// You can access the date field value inside the loop using currentrow."YEARMONTH"

END FOR;

END;

Thanks

SAI

lbreddemann
Active Contributor

You might want to take a closer look at the table "_SYS_BI"."M_TIME_DIMENSION_MONTH". It already contains many time formats and aggregation levels (week, year, ...) so that you don't actually have to SUBSTRing/CONCATenate your way around date formats.

Sharadha1
Active Contributor
0 Kudos

hi,

Ofcourse you can. use

DECLARE lv_begin DATE;

If you want to loop through the months in a year, use the table "_SYS_BI".M_TIME_DIMENSION after generating time data.

-Sharadha