Home / Q&A / Create new date field from a timestamp
You can translate the question and the replies:

Create new date field from a timestamp

Hi. I have two views in Denodo (in different datebases). I want to join the two on date (and another ID). However, the views do not have date, they only store timestamp. I want to get just the date from the timestamp in both views and join them. Here is what I've tried: * Create a new view * Join the two views on an ID * Attempt to add a join on date by converting the timestamp to date: Under join conditions, use complex condition to add the following join: to_localdate('yyyy-MM-dd', view1.evnt_ts) = to_localdate('yyyy-MM-dd', view2.end_timestamp) This did not work. So, I went into one of the view and added a new field called evnt_dt with the field expression of to_localdate('yyyy-MM-dd',view1.evnt_ts). When I executed this view, I noticed the field (evnt_dt) is actually null. Which explains why the above attempt of joining the views on a date did not work. My guess is to_localdate is not the correct expression to use here. Do anyone have an idea what I should use to get the date from timestamp?
timestamp Date
user
29-05-2020 17:31:58 -0400

2 Answers

You can check the second param of the function. it should be a text instead of a timestamp. TO_LOCALDATE( <localdate pattern:text>, <value:text> [, <language:text> ] ):localdate In this way, you can try FORMATDATE(‘<output_date_format>’, to_timestamp(‘<date_format>’, date)). Please notice that the FORMATDATE returns a string but not a datetime data type. And this string is created using the given pattern. To know more about how to write the pattern<output_date_format> according to your need please read the section Date and Time Pattern Strings. Hope this helps!
user
01-06-2020 03:15:53 -0400
Hi, To get only the date value from the text field type which contains the value of timestamp with time zone, I would use the** to_localdate** function to convert the text value into a date. For example, if I need to retrieve only the date value from "2019-12-21T13:15:49.000-0600" text value, then I would use the below similar query, `Select to_localdate('yyyy-MM-dd''T''HH:mm:ss.SSSSX', '2019-12-21T13:15:49.000-0600') from dual();` If you need to retrieve the only date value from the field type timestamp, then you could use the **Cast** function to converts data from one data type to another as similar below, > Cast('localdate', '2019-12-21T13:15:49.000-0600') For more information, you could refer to the below section of the * [TO_LOCALDATE](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions#to-localdate). * [Date and time Java patterns](https://docs.oracle.com/javase/7/docs/api/index.html?java/text/SimpleDateFormat.html). * [Cast](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/type_conversion_functions#cast). Hope this helps!
Denodo Team
01-06-2020 03:45:27 -0400
You must sign in to add an answer. If you do not have an account, you can register here