Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

how to check if a column contains valid date format

SamSurya
8 - Asteroid

I hve a column having date as 30-Jun-20. this column has different date formats. I need to check if the date given is in the format listed below:

 

The valid values are the following: 'DDMMYYYY' , 'MMDDYYYY' , 'YYYYMMDD' , 'DD-MM-YYYY' , 'MM-DD-YYYY' , 'YYYY-MM-DD' , 'DD.MM.YYYY' , 'MM.DD.YYYY' , 'YYYY.MM.DD' , 'DD/MM/YYYY' , 'MM/DD/YYYY' , 'YYYY/MM/DD' , 'YYYY-MM-DDThh:mm:ss.000000000' , if not  than flag as "Not Valid" else "Valid"

 

 

Please help. 

15 REPLIES 15
brendafos
10 - Fireball

I'll break it down for you:

 

^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$

 

^ = Start of Line

(19|20) = () defines a GROUP  and it says find either '19' or '20'

\d\d = find two digits - 0-9

[-/.] = then find - one of these from the set '-' or '/' or '.' where [ ] defines the set

(0[1-9]|1[012]) = GROUP find first a '0' then find 1 thru 9 OR (| = OR) find first a 1 then find 0 or 1 or 2

[-/.] = find either a dash '-' or '/' or '.'

(0[1-9]|[12][0-9]|3[01]) = now look for the last set of valid numbers sets - ( ) means this is a GROUP and 0[1-9] OR (the | means OR) [12] means find a 1 or 2 then [0-9] means find 0 thru 9, then another OR (| means OR) next find a 3 followed by either a 0 or 1

$ = end of string

 

Basically the first group checks for either 19xx or 20xx years ONLY

Then it looks for the two digits

Then it looks for choices of seperaters - dash dot or slash

Then it looks for valid date ranges for months - so a 30 would not match since there is no 30th month.

Then it looks for choices of seperaters - dash dot or slash

Last it looks for valid day of the month ranges.

 

 

brendafos
10 - Fireball

Look at it like this:

 

^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$

 

^ = start of string

(19|20)\d\d = match on start of 19 or 20, followed by two more digits

[- /.] = match on either a dash a slash or a dot

(0[1-9]|1[012]) = next match on a first digit of 0 followed by 1-9, OR (| = OR) match on a first digit of 1 followed by either a 0 a 1 or a 2 (so a 3 or greater will not match and you only get valid dates)

[- /.] = match on either a dash a slash or a dot

(0[1-9]|[12][0-9]|3[01]) = look for 3 valid combos (note the pipes | dividing the three choices) first 0 followed by either 1 thru 9 OR 1 or 2 followed by 0-9 OR starts with 3 and is followed by either 0 or 1

$ = end of string.

SamSurya
8 - Asteroid

Brilliant!!!... I always get problem with RegEX..... you have explained it so well now I will try by myself some string and number combinations...... Many thanks for the help.

SamSurya
8 - Asteroid

Sorry for asking so many questions...... This is fine perfectly with Normal Formula tool but If I',m trying to do same with IN-DB Formula tool , it is not working. so Is this only work with simple Alteryx tool  How can I do the same when fetching data from DB

I have tried as below:

 

If REGEX_Match("OFFER_EXPIRY_DATE", ^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$)
THEN 0
Elseif REGEX_Match("OFFER_EXPIRY_DATE", ^(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)\d\d$)
then 0
ElseIF REGEX_Match("OFFER_EXPIRY_DATE", ^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[012])(19|20)\d\d$)
THEN 0
Elseif REGEX_Match("OFFER_EXPIRY_DATE", ^(19|20)\d\d(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])$)
Then 0
Elseif REGEX_Match("OFFER_EXPIRY_DATE", ^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d$)
Then 0
Elseif REGEX_Match("OFFER_EXPIRY_DATE", ^(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])(19|20)\d\d$)
Then 0
ElseIF REGEX_Match("OFFER_EXPIRY_DATE", ^(19|20)\d\d[-](0[1-9]|1[012])[-](0[1-9]|[[12][0-9]|3[01])T(0[0-9]|1[0-9]|2[0123]??):(0[0-9]|[12345][0-9]):(0[0-9]|[12345][0-9]).\d+$)
Then 0
Else 1
Endif

brendafos
10 - Fireball

I do not think this will work IN-DB because at that point you need to write the SQL (T-SQL or P-SQL or ? SQL) and how SQL does RegEX is a different flavor of REGEX.  

Alteryx REGEX is base don PERL code - and I assume your data base does not run PERL code.

 

Could you pull the data into an Alteryx workflow, run the REGEX and then put the results set back in the DB.  

 

 

SamSurya
8 - Asteroid

Yep... got it... I have now pulled the data into Alteryx and now have worked using Formula tool. Really appreciate the efforts and swift response from Alteryx community..... A month back I started learning and exploring Alteryx which initially was a nightmare for me but with the support I have got from the community, really have helped me to learn alot.

 

THANKYOU 🙂

Labels