Hi all I have a file in which I want to change the date format.
input data
45426
Output Data
5/13//2024
Kindly guide me
@Kaish 5/14//2024? 😁
@Qiu , It's not working, I am getting null values, I have more than 1000 data, the above example was just a sample data, could you guide me how can I overcome this ? I have also changed the data type to date, still not working
@Kaish You have to at least show some of the records giving you null values...😑The variable for function ToDate() has to be integer.
Any solution where in I can select up the whole column and convert the data type to proper date ?
@Kaish can you add some sample data for the columnas I doubt there might be some other formats present in that columnwill be in better position to help.
Hi @Raj, this is the only sample I can provide, my date has this issue, I want to convert to a proper date that is for the whole column, the column contains dates in these values, I want to convert these values to a proper date.
In the dataset few data have proper date format "yyyy-mm-dd" and some have this excel number, I am trying to change the data type but still I am unable to et to my output
If its a mix match of date types you will need to use an IF statement to capture all of the date types in the column and how you want them to be output. We need more examples of all the formats available in your column.
Here is another way based on the two formats you had listed. If you are still getting nulls after this, we'll need to know which date formats are yielding null values.
if REGEX_Match([Field1], '\d{5}') then todate(tonumber([Field1])) elseif regex_match([Field1], '\d+-\d+-\d+') then datetimeparse([Field1],'%Y-%m-%d') else null() endif
Kindly, find the sample of my data set.
Just tested it and the code I put in my prior post works. Ive updated it to match the field name used in your example.
if REGEX_Match([Date], '\d{5}') then todate(tonumber([Date])) elseif regex_match([Date], '\d+-\d+-\d+') then datetimeparse([Date],'%Y-%m-%d') else null() endif
I am getting Malformed if statement with this formula, my incoming date column data type is V_string and the new output datatype is date. Kindly guide where I am facing the problem.
You might not have copied the entire code? I have the same settings.
As soon as I am typing '\d{5}' it starts throwing error 'Malformed if statement'. Can I know the reason ?
I had the same issue, but if you finish the complete statement it will work. Copy and paste from my prior two posts. If the name of the Date field is different, update the field name.
