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:
- Note that a pink circle in Trifacta denotes a space - so nothing is invisible!
Alternative way #1 - using the transform variation
You can also use the 'date format' transform (instead of the DATEFORMAT() function).
- The transform variation eliminates the need to write the function name itself.
- However, you can't nest a transform inside another transform\function
- Notice that we've used the "Custom format" option, as your desired date format isn't on the list.
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:
- {start} - denotes the beginning of the field
- {digit} - any given digit (0-9)
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.
