Hi, I need to reperform a DQ control with spotfire that requires me to verifiy that the format of each row of a column (data type : date) is "YYYY-MM-DD", how can I do this?

[Livia Alexandra Ivan]

Hi, I need to reperform a DQ control with spotfire that requires me to verifiy that the format of each row of a column (data type : date) is "YYYY-MM-DD", how can I do this?

[Fredrik Rosell 3]

Hello,

Not sure that this is exactly what you are looking for but here's a very basic example that illustrates how it could be done using a regular expression, and maybe you can find a way to apply this to your use case.

In this example, I have imported Col1 and Col2 (as a string), with the latter containing dates written in a few different ways.

I added a calculated column - Date Validation - that checks the format using the following regular expression

If([Col2]~="^\d{4}-\d{2}-\d{2}$",true,false)

Note: Technically this only checks for digits - not specifically for valid dates - just to keep the example very simple.

You can easily find examples of regular expressions for valid date checks on the internet, e.g. the first one I found was

https://www.regular-expressions.info/dates.html. Make sure to use an expression that fits your particular needs.

For more information on regular expression syntax in general, please refer to general sources on this on the internet.

Best Regards

Fredrik