Skip to Content

DATE declaration in SAP HANA Stored Procedure

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jan 18, 2017 at 02:42 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 18, 2017 at 07:20 PM

    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

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