You can translate the question and the replies:

Convert text to Timestamp

Hello. I am trying to convert a text to timestamp: * My text has this format dd-MMM-yyyy HH:mm:ss * I am using this function to_timestamp('dd-MMM-yyyy HH:mm:ss', bv_ho_orders.date_placed) * When I convert to timestamp some of the dates are null. * The dates that are not null come in this format 2014-07-30 15:48:11, which corresponds to this 30-Jul-2014 15:48:11 * But, for example, this date 20-Oct-2014 05:04:09 is null. Why is it not working? Thank you
user
05-05-2021 05:32:08 -0400

2 Answers

Hi, Using the to_timestamp() function, I am able to convert timestamps from a variety of text representations of date time. I can see in the function call here that a single timestamp pattern is used for the entire column, which can produce null outputs for the rows whose text does not match the given pattern. Since the to_timestamp() function takes the timestamp pattern to be used as its first argument, I will need to make another function call with the appropriate pattern in order to convert these differently formatted text strings to timestamps as expected. For more information on writing these patterns, please follow the [definition of the to_timestamp() function](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions#to-timestamp), and the [Date and Time Pattern Strings ](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/appendix/date_and_time_pattern_strings/date_and_time_pattern_strings#date-and-time-pattern-strings)section of the VDP VQL Guide. Additionally, I would open the relevant View in the Virtual DataPort Administration Tool, and click on the Summary panel to verify columns used for my function inputs are of Field Type ‘text’ as expected. Hope this helps!
Denodo Team
05-05-2021 17:50:10 -0400
Hello, Thank you for you answer.
user
13-05-2021 11:23:10 -0400
You must sign in to add an answer. If you do not have an account, you can register here