1

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 ?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
user4836066
  • 51
  • 1
  • 3
  • 7

3 Answers3

2

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.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
1

@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)';
djk
  • 943
  • 2
  • 9
  • 27
-1

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)
I_am_Batman
  • 895
  • 9
  • 21
  • Oracle and SAP HANA are very different when it comes to the script language. Explaining by analogy doesn't work in this case. – Lars Br. Apr 29 '16 at 14:04
  • @LarsBr., ok, point noted. So are we sure that this extended analogy will not work in this case? A quick browse had shown me that similar syntaxes exist in HANA. – I_am_Batman Apr 29 '16 at 17:50
  • @user4836066, if you have tried and my solution did not work, do state it here. I would then remove the answer. – I_am_Batman Apr 29 '16 at 17:51
  • the Oracle syntax solution does not work on SAP HANA. There is no DBMS_OUTPUT in SAP HANA, the equivalent to DUAL is DUMMY and to use use variables the colons have to be in place. So technically speaking your solution is not working. But the main problem, as far as I understood it, wasn't the date format parsing in the first place, but just the wrong syntax. – Lars Br. Apr 29 '16 at 22:42