CODEWITHSUNDEEP
stored-procedureshanahana-sql-script
user4836066
user4836066

Reputation: 51

Add days to date in SAP HANA stored procedure

I need to add days to date in HANA stored procedure, but I am getting error message

ERROR WHILE parsing DATE/ TIME

I use this statement where p_end_date is parameter of my stored procedure.

v_end_date_plus := add_days (TO_DATE(' || p_end_date || ' , 'YYYY-MM-DD' ), 90)

Is there is any other way or what I am doing wrong in it ?

Upvotes: 1

Views: 11831

Answers (3)

djk
djk

Reputation: 973

@LarsBr. is correct that you need a colon (:) to reference the variable, and that if it is really a DATE type, you don't need to convert TO_DATE again.

But additionally, in your example you have some mixup with quotes and concatenation that makes me think that you actually want to construct some character string using p_end_date. This would need conversion to a date first:

p_end_date := '2016-05-03'; -- for example
v_end_date_plus := add_days(TO_DATE( :p_end_date , 'YYYY-MM-DD' ), 90);

The part ' || p_end_date || ' in your example also looks a bit like the whole code was actually part of string to be used in EXEC or similar. If that's the case, you need to have escaped single-quotes for both parameters, e.g.

exec 'v_end_date_plus := add_days(TO_DATE(''' || :p_end_date || ''', ''YYYY-MM-DD'' ), 90)';

Upvotes: 1

I_am_Batman
I_am_Batman

Reputation: 915

p_end_date should be a varchar field, or the appropriate string literal used in your technology. It should not be enclosed in quotes.

v_end_date_plus := add_days (TO_DATE(p_end_date , 'YYYY-MM-DD' ), 90)

EXPLANATION USING ORACLE AS REFERENCE :

In Oracle database, the default date format is dd-MON-RR or dd-MON-YYYY.

So, if I use correct date format to p_end_date variable, I am able obtain output. enter image description here

However, if I diverge from this default format,my attempt would error out.

enter image description here

So, if I want the flexibility to redefine p_end_date in format of my choice, and not as per default settings, it should be a String literal.(varchar in Oracle ).

EDIT:

The essence of this answer was just to suggest that the variable should be passed as a varchar. Borrowing from Lars Br's suggestion below to modify the p_end_date variable's syntax: 

v_end_date_plus := add_days (TO_DATE(:p_end_date , 'YYYY-MM-DD' ), 90)

Upvotes: -1

Lars Br.
Lars Br.

Reputation: 10396

Even though you didn't post what error you receive, I guess that the problem in your code is the way you referenced your input variable.

v_end_date_plus := add_days ( :p_end_date , 90);

With the colon (:) in front of the parameter you should be able to use it without having to cast it into a different data type.

Upvotes: 2

Related Questions