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. 

[Balu22]

Thankyou Bheemesh. I will look into it.

[bheemeshRao]

Hi Balu,

You can do transformation in the data flow by adding a calculated column in a projection and convert it to your desired format using date functions or by a python script using a script task.

We cannot validate, but only transform.

[Balu22]

Thankyou Bheemesh.

yeah by inbuilt Date functions we can do only transform to a desired format.

But I'm looking for python script to validate Date format whether it is Valid or Invalid. Its a business requirement in my case.

I found many Python scripts to validate Date format, those are working fine in Python Editor but not in Datasphere.

Might be it has some limitations. 

[bheemeshRao]

Hi Balu,

could you please let us know what exactly you are trying to achieve?

Are you trying to bring in data to datasphere via a replication flow? where do you want to validate the date?

[Balu22]

Hi Bheemesh,

I would like to validate Date format in Dataflow but I don't see any inbuilt function to validate Date format.

I want to validate Date format coming from source table or File whether it is matching with Business requirements.

Example:  Date should come as 'YYYY-MM-DD'

Is there any inbuilt function to validate or any python script to validate Date Format.

Thanks,

Balu