Hi I have a string dates like below and I want to change it into date data.
2 format of data is present below.
1. yyyy-mm-dd
2. dd/mm/yyyy
Date |
2020-01-17 |
7/07/2022 |
2021-02-16 |
15/03/2023 |
Now 2 changes we have to do in the above input
1. Change String to date (Data Type)
2. Change the format to yyyy-mm-dd
Output:
Date |
2020-01-17 |
2022-07-07 |
2021-02-16 |
2023-03-15 |
Hi @Sshasnk
Please use Multi field formula.
Also use datetimeparse to convert the date.
IF contains([_CurrentField_],"/")
THEN datetimeparse([_CurrentField_],"%d/%m/%y")
ELSE [_CurrentField_]
ENDIF
Many thanks
Shanker V
Check this Macro out.
https://community.alteryx.com/t5/Community-Gallery/BB-Date-Python-Macro/ta-p/1043041
Hey @Sshasnk, the following expression should do the trick:
IF Contains([Date], '/') THEN DateTimeParse([Date],'%d/%m/%Y') ELSE DateTimeParse([Date],'%Y-%m-%d') ENDIF
Hi, @Sshasnk
It is a little more nuanced because you have the date 7/07/2022 (other incoming dates taking this format could be of form MM/dd/YYYY or dd/MM/YYYY).
You will need to FIRST separate each incoming format to PROFILE its population of dates and then determine and deploy the applicable treatment, before unioning back into a standardized Date output stream (e.g. is 6/08/2023 = Jun 6th or Aug 6th?)
However, for your limited use case below, consider the below which has some QC built-in. I hope you find it helpful - Cheers!