Date Format issue
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 Name | Date Format |
QCT | 5/22/2021 |
CORP | 01/30/2021 |
CLOUD | 25-Nov-16 |
I need a "YYYY/MM/DD" format So, Can you please help me out
Solved! Go to Solution.
- Labels:
- Developer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Lalaiah
You can use a if else block check for date format does it contain "-" or so based on it use dateparse function.
IF Contains([Date Format], "-")
THEN DateTimeParse([Date Format],"%d-%b-%y")
ELSE DateTimeParse([Date Format],"%m/%d/%Y")
ENDIF
Hope this helps 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Lalaiah ,
I've used the DateTime tool to convert, then merged the different versions.
Hope this helps,
M
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@atcodedog05 @MarqueeCrew @mceleavey
It is rare to see big shots working on the same question. 😁
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.