I have one date format like below sreenshot,I want to change the green part to dd like 03 04 ,dd has two characters ,how can I get the result shoot
Hi Wu,
Use the function DATEFORMAT(Column_name, 'dd.MMM.yyyy').
Best,
Apeksha
Hi @wu cong? , thank you for your question =]
As @Apeksha Prasad? has mentioned, you can use the DATEFORMAT() function, which converts a DateTime into a custom format.
By using what she had written, you will get the following:
As you can see, the day element of the date has now become 'dd', meaning it is being represented by 2 digits.
However, if you'd like the separator between the date elements to remain a space (as is in your original example) as opposed to a dot, you can simply replace the dots in the format with a space:
Alternative way #1 - using the transform variation
You can also use the 'date format' transform (instead of the DATEFORMAT() function).
alternative way #2 - using Trifacta patterns
While a bit more complicated, patterns can get us out of many sticky situations; this is one of them.
We can ask to replace any single digit at the beginning of the field with a zero, followed by that digit:
We then put parenthesis around the digit (including the following space) to be used as a capture group (see doc here).
In the 'replace with' field, we put zero, followed by '$1', which references the first capture group.
Alternative way #3 - transform by example
You can choose the "create column from example" option from the drop-down list of the column:
Then, in the window that opens - guide Trifacta to your desired date format. For example - add a zero before one of the "lonely digits", and write the rest of the date as is:
Once you hit Enter, Trifacta will use that suggestion and change the other values accordingly:
That's it.
I hope that makes sense; please feel free to ask any additional questions.
Thanks,
Amit.