how to check if a column contains valid date format

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.

Accepted answers

DavidP

I've updated it now with all the options

valid date.yxmd

JosephSerpis

Hi @SamSurya you can do this with Regex match and also check that the date dosent exceed 12 months or the date go past 31 days. Let me know what you think?

Regex_Match_Date_22072020.yxmd

brendafos

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

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.

All comments

DavidP

You would do it with a formula like this. You'll have to test for every type of format in your list.

I'll add some more if I get a chance.

valid date.yxmd

DavidP

I've updated it now with all the options

valid date.yxmd

JosephSerpis

Hi @SamSurya you can do this with Regex match and also check that the date dosent exceed 12 months or the date go past 31 days. Let me know what you think?

Regex_Match_Date_22072020.yxmd

SamSurya

Wow... Thanks a tonne... This works for me and yes, you gave me a thought, I do get data where dates are in valid format but not correct this check would indeed be really helpful. so is this possible with in Regex or do we need to have additional formula.

brendafos

Joseph -

Love your solution.

Thanks for another great example of the power of Regular Expressions!

SamSurya

Hi Joseph,

just one more help, could you please help me understanding ""^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$" .

Thanks

Sam

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703