CODEWITHSUNDEEP
sqlhana
Samadung
Samadung

Reputation: 19

HANA Error while parsing "11/11/2018 12:00:00 AM" as DATE

I'm trying to do this 

CREATE PROCEDURE Create_test(IN datefrom date
)
as

begin
select add_years('0',years_BETWEEN('0', :datefrom)-1 )from dummy;
end

When I run it it's fine, with no error, the procedure is created. But when I try to call the procedure it's give me this error

SAP Hana Database Error: invalid DATE, TIME or TIMESTAMP value: Error while parsing 11/11/2018 12:00:00 AM as DATE at function typecast() (at pos 31)

Please help

Upvotes: 0

Views: 14802

Answers (2)

Lars Br.
Lars Br.

Reputation: 10396

The error message is likely caused by the way the procedure is called, i.e. the input parameter.

When you enter an actual date data type (e.g. return value from a function like current_date, the result of a type conversion like to_date ('21/02/2018', 'DD/MM/YYYY') or a date literal date'2018/02/21' ) no error gets raised.

So, as you've written in your answer, the problem is the text date format that you put into the procedure. In such a case SAP HANA will try to automatically convert the value into a date data type and fails if the format is not one of the documented standard formats.

This is another example of why implicit type conversion is something to be very careful with.

Concerning the actual goal of the procedure, finding the first date of the current year, there is a much easier way to achieve that.

The function current_date returns the current date (in server timezone). Taking the YEAR component of that date can easily be done with the year() function. Finally, converting this to a date (daydate/seconddate) data type fills up the day components with the lowest possible value: that's the first day of the year.

select to_date(year(current_date)) NEW_YEAR_DATE from dummy

Upvotes: 1

Samadung
Samadung

Reputation: 19

I know the answer. The editor that I use which is TOAD for SAP Solution give me a wrong date format when I execute the procedure. It give me 'DD/MM/YYYY' format, the right one is 'YYYY/MM/DD'.

Upvotes: 0

Related Questions