is_valid_date and raise an exception

[former_member185332]

SAP DS version 4.2 SP 6

I have a datetime field with the following code

ifthenelse(

is_valid_datetime(Q_SELECT.TIJDSTIP, 'YYYY-MM-DD HH24:MI:SS')=1,

to_date(Q_SELECT.TIJDSTIP,'YYYY-MM-DD HH24:MI:SS'),

raise_exception ('for the given inputfile an value not equel to timestamp is found on field TIJDSTIP, hence aborted')

)

with validation this code is invalid:

[Query:Q_check_datatypes]

Invalid mapping expression for column <Q_check_datatypes.TIJDSTIP>. Additional information: <Cannot parse expression.

For the call to function <ifthenelse>, cannot convert parameter number <3> to type <INT>. Provide a parameter of the correct type.. (BODI-1112351)>. (BODI-1111081)

the only way around i can image is to split the functionality into 2 field, one for test and raise on invalidation of the timestamp and 1 for the actual conversion to datetime when the inputfield is valid....

Is my code indeed not correct or is this a bug in DS?

[former_member198401]

Hi,

Best practice for handling dates would be to mark the invalid date as NULL or a future date rather than raising an exception. You cannot use raise_excption() in the mappings as there are so many dates which might contain invalid values. You can set it in a script for e.g. if the Date contains values like 00000000 then raise an exception.

Can you try the below

ifthenelse(

is_valid_datetime(Q_SELECT.TIJDSTIP, 'YYYY-MM-DD HH24:MI:SS')=1,

to_date(Q_SELECT.TIJDSTIP,'YYYY-MM-DD HH24:MI:SS'),null)

)

Regards

Arun Sasi

[vnovozhilov]

The logic behind the mapping is to check if the input value is recognized as a valid datetime, otherwise flag it with an exception. The raise_exception() may or may not be handled later. The function always return INT 1.

According to SAP Data Services Reference Guide

http://help.sap.com/businessobject/product_guides/sbods42/en/ds_42_reference_en.pdf

6.3.61 ifthenelse

Syntax: ifthenelse(<condition>, <true_branch>, <false_branch>)

If the data type of <false_branch> is not convertible to the data type of <true_branch>, Data Services produces an error at validation. If the data types are convertible but don't match, a warning appears at validation.

Therefore, you would need to find an alternative way.

Thank you,

Viacheslav.