Skip to Content
0

DATE declaration in SAP HANA Stored Procedure

Jan 18, 2017 at 09:21 AM

161

avatar image

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

SQL
10 |10000 characters needed characters left characters exceeded

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.

0

thanks Everyone, for your inputs.

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Sharadha K Jan 18, 2017 at 02:42 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
LINGALA SAI Jan 18, 2017 at 07:20 PM
0

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

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

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.

1