Skip to main content

Hi,

I have a table where a datetime column is present with several records:

number    datetimecolumn
1              2024-02-01 00:00:00
 2             2024-02-02 00:00:00

I 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?

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!

 

 

 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 are YEAR, MONTH, DAY, HOUR, MINUTE and SECOND.
To check for the first day of month you can use following condition:
datePart(ATTRIBUTE_1, 'DAY') = 1

 

 

Thank you both for the replies!

Reply


Community Terms of Use & ConditionsCookie settings