Some datetime values display blank instead of 00. Trying to replace those instances by the same value suffixed with '00' so that all my values end up with the same consistent format: mm/dd/yyy hh:mm:ss.

[TrifactaUsers]

I got a datetime column with format like 5/5/2019 5:37:49. Some of the values display blank when the value is 00, example: 5/5/2019 5:37, when it's actually 5/5/2019 5:37:00. I am trying to replace those instances by the same value suffixed with '00' so that all my values end up with the same consistent format: mm/dd/yyy hh:mm:ss.

I tried using the Replace function but it's not that obvious.

How can I do it?

[Trifacta_Alumni]

Hi @RAMZI EL HADDAD?,

Based on the examples give, you can try this:

replacepatterns col: End with: '$1:00' on: `^([0-9]+\/[0-9]+\/[0-9]+ [0-9]+:[0-9]+/)$`

Cheers,

Nathanael

[TrifactaUsers]

Thanks @Nathanael Kuipers? . Unfortunately I'm not having the expected result. What this is doing is duplicating the same exact values to the new column. What I need to have is: 4/2/2019 8:20 should be transformed to 4/2/2019 8:20:00. I've attached a screenshot of the result using your suggestion. I'm using Trifacta Pro 5.1, not sure it's any different from the newer versions. Thanks again!

[Trifacta_Alumni]

The reason is because I was in a rush. If you replace the ` character on either end of the regular expression with / then you should be fine.

[TrifactaUsers]

Hey @Nathanael Kuipers? , no worries.

What do you call regular expression?

replacepatterns col: End with: '$1:00' on: /^([0-9]+\/[0-9]+\/[0-9]+ [0-9]+:[0-9]+/)$/ generates an error

Invalid language syntax cannot be pasted into Transform Builder:

replacepatterns col: End with: '$1:00' on: /^([0-9]+\/[0-9]+\/[0-9]+ [0-9]+:[0-9]+/)$/

replacepatterns col: End with: /$1:00/ on: '^([0-9]+\/[0-9]+\/[0-9]+ [0-9]+:[0-9]+/)$'

gives the same result.

Please give the complete expression. Thanks.

[Trifacta_Alumni · Accepted solution]

/^([0-9]+\/[0-9]+\/[0-9]+ [0-9]+:[0-9]+)$/

[TrifactaUsers]

Working! Many thanks @Nathanael Kuipers? 

[TrifactaUsers]

Here is the right function: replacepatterns col: End with: '$1:00' on: /^([0-9]+\/[0-9]+\/[0-9]+ [0-9]+:[0-9]+)$/