You can translate the question and the replies:

TEXT to DATE conversion questions

I am using Denodo 8.0 and have two different date conversion questions. First scenario I have a “date” field that is defined as a TEXT type on the source file. The format is dd-MMM-yy. I want it to be a localdate type (yyyy-MM-dd) I am trying to convert it to a date field in the Derived view and I am using this expression: to_localdate('dd-MMM-yy', bv_ individual.startdt) The source value is: 30-APR-85 The data shown in the date field when executing the derived view is 2085-04-30 instead of 1985-04-30 What do I need to add to the expression to indicate that the resulting date should be not be a date in the future? Second scenario: I have a “date” field that is defined as a TEXT type on the source file. The format is dd-MMM-yy HH:mm:ss.SSSSSSSSS aa. (example: 11-APR-19 03.59.51.311563000 PM ) I am trying to convert it to timestamp field in the Derived view and I have tried using different expressions (shown below), but the result when executing the derived view is always that the date contains a null value. What field expression should I be using to accomplish my goal? to_timestamp('dd-MMM-yy HH:mm:ss.SSSSSSSSS a ', bv_organizational.revocationdate) to_timestamp('dd-MMM-yy HH:mm:ss.SSSSSSSSS aa', bv_organizational.revocationdate) to_timestamp('dd-MMM-yy HH:mm:ss.SSSSSSSSS aa ', bv_organizational.revocationdate) formatdate('dd-MMM-yy HH:mm:ss.SSSSSSSSS a',bv_organizational.revocationdate)
user
22-04-2022 09:07:57 -0400
code

1 Answer

Hi, When using the date format 'd-MMM-yy', the value of year will depend on the current century. This because the date functions use the new java.time API for parsing and formatting dates, [java.time.DateTimeFormatter](https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html). This parser always interprets yy with 2000 as base year. For example: >select to_localdate('d-MMM-yy', '1-Jan-93') from dual(); The output of the above query will be '2093-01-01' which is expected (based on the current century). However, in cases where I want to receive a different year, then I would execute a query in the format as specified below: >select to_localdate('d-MMM-yyyy', '1-Jan-1993') from dual(); This will give an output '1993-01-01' with '1993' as the year. Regarding the second question, I would be able to convert text to timestamp using the below syntax: >SELECT CAST('timestamp',CURRENT_TIMESTAMP),formatdate('yyyy-MM-dd h:mm a', CAST('timestamp',CURRENT_TIMESTAMP)) AS format_date I could see a 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. For more information on writing these patterns, please follow the 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. Hope this helps!
Denodo Team
25-04-2022 08:23:31 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here