DQ Rule Datetime

Question

Hi,I have a table where a datetime column is present with several records:number    datetimecolumn1              2024-02-01 00:00:00 2             2024-02-02 00:00:00I want a DQ Rule that filters out the datetime records that do not start with the first of the month. Meaning number 1 is valid, number 2 is invalid. I couldn't find anything useful in the condition builder option and the advanced expression (even through documentation in ONE expression) is giving me headache. Any suggestions how to fulfill this?

AKislyakov · Accepted Answer

Hi @JTH,If your datetimecolumn is of type Datetime or Date, you can use datePart(date, part) function to extract the day number. Possible part values areYEAR, MONTH, DAY, HOUR, MINUTE and SECOND.To check for the first day of month you can use following condition:datePart(ATTRIBUTE_1, 'DAY') = 1

Samuel Muvdi · Answer

Hello! if what you want is for your rule to mark valid only dates that match that pattern of having the first of the month be valid only, I recommend using the advanced expression setting and writing in this:

matches('^([0-9]){4}-([0-9]){2}-(01) ([0-9:])*$', Date)

You would have to set the date column as a string, not as datetime, since you will be using regex to match the pattern you want.

Here’s a sample of how it would look:

Let me know if you need any more help!

DQ Rule Datetime

3 replies

Reply

Reply

Sign up

Login to the Ataccama Community

Scanning file for viruses.

This file cannot be downloaded