0

I have an expression transformation which takes a UTC format string and transform it to datetime object like this:

 to_date(SUBSTR(from,0,10) || ' ' || SUBSTR(from, 12,8),'YYYY-MM-DD HH24:MI:SS')

However, when I check the result, it is not in the format I declared, this is the result:

"06/01/2023 10:00:00"

Note that this is the input:

"2023-06-01T10:00:00.00Z"

My purpose is actually adding 1 day to the input, I want to change the 2023-06-01T10:00:00.00Z to 2023-06-02T10:00:00.00Z. But to do that, I need to first convert the string to date object so that I can use the ADD_TO_DATE() function to add 1 day, that's why I am converting the UTC string to date object here.

Thank you so much.

La. Li
  • 41
  • 6
  • I have solved this problem, it is because the format is referring to the format to parse the input but not the format of the output here. – La. Li Jun 06 '23 at 14:28
  • And I need another seperate to_char(date) function to get the format I want eventually. Hope this helps if you have similar question. – La. Li Jun 06 '23 at 14:29
  • 1
    Just a comment here: DATE is a number. It has NO format. If you need it in any particular format, you need to conver it to STRING. You also use the format fot the TO_DATE function to tell what is the input STRING format, to convert it to DATE - that will no longer have any format. Hope this helps :) – Maciejg Jun 12 '23 at 14:46
  • 1
    Thank you for sharing the knowledge! @Maciejg – La. Li Jun 13 '23 at 18:10

0 Answers0