You can translate the question and the replies:

Format Odd TEXT Date to Timestamp

I have a field called date that is coming in as text as the following format and I am having trouble converting it to a valid time stamp: 9 - Sep - 2019 19:35 Can anyone help with the formatting of the function?
user
09-08-2021 11:12:02 -0400

4 Answers

This seems to work --- but not for every record. to_timestamp('dd - ‎MMM - yyyy HH:mm', time) It works for this value: 11 - ‎Jul - 2019 14:30 but produces a null result for this value: 19 - Jul - 2019 14:02 There seems to be some kind of "extra" space (‎) in the majority of the "time" values--- not sure how to strip them out to make the formula work consistently.
user
09-08-2021 12:05:52 -0400
This is the special character appear in some of the time values: *‎ Not sure how to strip it out.
user
09-08-2021 12:13:37 -0400
I got it working!!!! this is the regext I ended up using regexp(time, '[^-0-9A-Za-z -:]+', '') it removes the odd characters and keeps the spaces/dashes and colon then the timestamp calc worked for ALL records.
user
09-08-2021 12:44:17 -0400
Hi, I would use the regex as well in this scenario because there is not a lot of uniformity in the way that the times are written. In most cases with type text time values, I would use the TO_TIMESTAMP function to pull out the values in the correct format.For overview of this function please checkout the [TO_TIMESTAMP](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions#to-timestamp) section of the Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
10-08-2021 17:57:31 -0400
You must sign in to add an answer. If you do not have an account, you can register here