on 01-18-2017 9:21 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.