You can translate the question and the replies:

Text (timestamp) to date

Hi. My view has a field with timestamp. Although, the field type is "text". I really only want to get a date from the field, but I don't know how. Here is an example of the text field: 2019-12-21T13:15:49.000-0600 2019-10-02T15:01:43.000-0500 I need to be able to join this to another view on date. The other view has a date in the following format: 2020-05-05 (returned using to_localdate on a timestamp field). Ideally, I'd like to be able to get the text field above to return a date in similar format: 2019-12-21, 2019-10-02. I thought I would try to_localdate or to_timestamp. But, I think I'm getting the expression wrong due to the way the text is formatted (things I tried): to_timestamp('yyyy-MM-dd''T''HH.mm.ss.SSS', end_timestamp) to_timestamp('yyyy-MM-dd''T''HH.mm.ss.SSS''Z', end_timestamp) to_localdate('yyyy-MM-dd''T''HH.mm.ss.SSS', end_timestamp) to_localdate('yyyy-MM-dd''T''HH.mm.ss.SSS''Z', end_timestamp) I'd appreciate any suggestions.

Timezone conversion Date Date Conversion Dates

user
30-05-2020 15:09:39 -0400

2 Answers

There are 2 obvious errors:1)in your text time seperator is ":", but the timestamp you use "." 2)The T and Z can use revised as follows: select to_timestamp('yyyy-MM-dd''T''HH:mm:ss.SSSZ','2019-12-21T13:15:49.000-0600') 2019-12-22 03:15:49 Try it from you side. Denodo at your service!

user
01-06-2020 03:39:06 -0400

Hi, In order to get the date value from the text value, you could use the **to_localdate** function to convert the text value into a date. For more information, you can refer to the similar community question [Create new date field from a timestamp](https://community.denodo.com/answers/question/details?questionId=9060g0000004FRxAAM&titleCreate%20new%20date%20field%20from%20a%20timestamp). Hope this helps!

Denodo Team
01-06-2020 03:55:21 -0400

ADD ANSWER

You must sign in to add an answer. If you do not have an account, you can register here