Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Date Format issue

Lalaiah
8 - Asteroid

Hi,

Consolidated 3 files but the date formats are different for each file so when I convert into date format with help of the date-time tool. however, I am getting below error."ConvError: DateTime (82): Payment Due Date1: Cannot convert "06/30/2020" to a date/time with format "%Y-%m-%d": Month number is out of range 1..12: '30/2020' Record #48433"

 

File NameDate Format
QCT5/22/2021
CORP01/30/2021
CLOUD25-Nov-16

 

I need a "YYYY/MM/DD" format So, Can you please help me out

19 REPLIES 19
atcodedog05
22 - Nova
22 - Nova

Hi @Lalaiah 

 

You can use a if else block check for date format does it contain "-" or so based on it use dateparse function.

atcodedog05_0-1621943709623.png

 

IF Contains([Date Format], "-") 
THEN DateTimeParse([Date Format],"%d-%b-%y")
ELSE DateTimeParse([Date Format],"%m/%d/%Y") 
ENDIF

 

Hope this helps 🙂

 

mceleavey
17 - Castor
17 - Castor

Hi @Lalaiah ,

 

I've used the DateTime tool to convert, then merged the different versions.

 

mceleavey_1-1621943792503.png

 

 

mceleavey_0-1621943780379.png

 

 

Hope this helps,

 

M



Bulien

MarqueeCrew
20 - Arcturus
20 - Arcturus

@Lalaiah ,

 

 let's try:

 

if substring ([date field], 2,1) = "-" then 

datetimeparse([date field],"%d-%b-%y")

else

datetimeparse([date field],"%m/%d/%Y")

endif

 

 cheers,

 mark


https://community.alteryx.com/t5/Engine-Works/MarqueeCrew-s-Guide-to-Avoiding-Date-Frustration/ba-p/... 

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
atcodedog05
22 - Nova
22 - Nova
Qiu
21 - Polaris
21 - Polaris

@atcodedog05  @MarqueeCrew  @mceleavey 
It is rare to see big shots working on the same question. 😁

mceleavey
17 - Castor
17 - Castor

big deal.gif



Bulien

Lalaiah
8 - Asteroid

Hi 

Thank you for your quick response !!!

 

Getting the same error .. I'm attaching an example file can you please sort it out.

 

Thanks in advance.

Regards,

LAlaiah 

mceleavey
17 - Castor
17 - Castor

Hi @Lalaiah ,

 

Sorry, what's the problem you're having? What was the error?

 

M.



Bulien

MarqueeCrew
20 - Arcturus
20 - Arcturus

@Qiu 

 

I'm a fan of not using the date time tool. I think I've seen more data questions still than regular expression questions. Certainly they are amongst the top 10 questions on community. 

in my early use of Alteryx I would use string functions to construct dates. Now I refer to date specified and have them on speed dial. 

stopping in my coffee, a date question is a familiar way to start my day. 

cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels
Top Solution Authors