You can translate the question and the replies:

todate from text string

I have a string with date in the following format: 2017-02-08T16:17:10Z tried to use this function but it is NOT working to_timestamp('yyyy-MM-dd''T''HH:mm:ssZ', createdat) I also tried this: to_timestamp('yyyy-MM-dd''T''HH:mm:ss''Z', createdat) What am I missing?
user
09-11-2021 14:59:13 -0500
code

5 Answers

Hi, Date and Time formats in the Virtual DataPort are based on the[ Java patterns](https://docs.oracle.com/javase/8/docs/api/index.html?java/text/SimpleDateFormat.html). In certain cases, I believe that the conversion expects certain values based on which the conversion would happen and in this scenario, I think that due to this reason, NULL is being returned. I tried executing a query based on the below format in the VQL Shell which gave me timestamp value: `select to_timestamp('yyyy-MM-dd''T''HH:mm:ssz','2017-02-08T16:17:10Z') from dual();` For more information, you could take a look at 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 under Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
10-11-2021 07:29:17 -0500
code
I have another field in the source that is slightly different... multiple decimal places but the formula below is not working (comes up null): 2021-08-17T10:48:39.9766667Z to_timestamp('yyyy-MM-dd''T''HH:mm:ss', substr(bv_cloudera_edl_summit_vw_rpt_transcript._last_touched_dt_utc,0,19)) tried to simplify and remove the decimal and Z and the convert. let me know if I am missing something
user
01-12-2021 14:43:17 -0500
It looks like query in Denodo is getting changed from to_timestamp to this: **from_unixtime(unix_timestamp**(SUBSTR(t0.user_lo_comp_dt,0,19),'yyyy-MM-ddTHH:mm:ss')) that seems to be causing an error 2 against the apache haddop hive source. very odd
user
01-12-2021 15:23:27 -0500
Hi, Virtual DataPort uses the date and time Java patterns to specify date and time formats. When the to_timestamp function is used, it expects a text value with datetime in a specific format which should be in accordance with Java’s syntax ‘java.text.SimpleDataFormat’. In case if the values are not in the expected format, then the output would be null. In this scenario, I would suggest you check more on the format of the text value being sent to the function such that its pattern is acceptable. For more information,you can take a look at the [TO_TIMESTAMP](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 under the Virtual DataPort VQL Guide. Further, when queries are executed against a JDBC data source, then there can be certain query translations happening based on the adapter being used (Hive, in this case). In case you need further assistance and if you have a valid Support Account then you can reach out to Denodo Support by raising a support case on [Denodo Support Site ](https://support.denodo.com/)and the Support Team will assist you. Hope this helps!
Denodo Team
02-12-2021 04:15:25 -0500
code
Yes! I found that this field had values that were written out as "NULL" and not actually null so I had to use a formula like this: case WHEN (user_lo_comp_dt = 'NULL') THEN NULL ELSE to_localdate('yyyy-MM-dd', substr(user_lo_comp_dt, 0, 10)) END I ended up using Substr to only keep the date (I ended up not needing the time information for this use case) which simplified the request.
user
02-12-2021 08:05:41 -0500
You must sign in to add an answer. If you do not have an account, you can register here