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?
Solved
DQ Rule Datetime
+2- Data Pioneer
Best answer by AKislyakov
Hi
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
- Ataccamer
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!
- Ataccamer
Hi
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
Reply
Login to the Ataccama Community
No account yet? Create an account
For Ataccama Customers and Partners
or
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.