Date format validation in SAP Datasphere

[Balu22]

Hi Team,

please help me to validate Date format which is coming from source table or file  in SAP Datasphere.

Thanks,

Bala

[bheemeshRao]

Hi Bala,

[bheemeshRao]

Hi Balu, if this suffice your requirement https://community.sap.com/t5/technology-q-a/determine-if-valid-date-via-sql/qaq-p/9506869

[Martin_Kuma]

You will need to decide what is to happen when  the validation fails. You want to break the load? Break the SAC story? Or just show an ERROR? Something you can do with the automatic lookup in BW TRFNs?

Python -> pandas is automatically imported into DSP, so just search for "python pandas docu...": https://pandas.pydata.org/docs/user_guide

but... 

python is on a separate engine in DSP, so it will be slow. Very slow. Whereas SQL is on HANA, so I would go for SQL HANA functions. For example, if you expect YYYYMMDD, then do SELECT CASE WHEN LTRIM(<date_field>, 0, 4) <> (1800,...,2099) ELSE ' ', ...

You can use LENGTH to check if the string has 8 characters

EXTRACT year, month, day and then validate these if in interval

... check the HANA SQL Library. 

[Balu22]

Thankyou Martin.

I just want to show it as valid or invalid date in a separate Flag column.

Thanks for the suggestion. I will look into it.