How do I convert date in Netezza to yyyymmdd from timestamp format?

Question

How do I convert date in Netezza to yyyymmdd from timestamp format?

Delciedelcina

Not a duplicate, the other question is about going from number to date. – Urgency

Answer 1

Use the below queries to convert to date format.

select TO_CHAR( DATE '2009-12-23 23:45:58','YYYY-MM-DD')

or

select TO_CHAR(TO_DATE( '2009-12-23 23:45:58','YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD')

or

select TO_CHAR(current_timestamp,'YYYY-MM-DD')

Lioness

You didn't answer OP's question, but you gave a stepping stone, to: TO_CHAR(current_timestamp, 'YYYYMMDD') as DATE_AS_STRING_YYYYMMDD So, thanks in any case ... it was still helpful! – Whacking

Answer 2

Netezza has built-in function for this by simply using:

SELECT DATE(STATUS_DATE) AS DATE,
       COUNT(*) AS NUMBER_OF_             
FROM X
GROUP BY DATE(STATUS_DATE)
ORDER BY DATE(STATUS_DATE) ASC

This will return just the date portion of the timetamp and much more useful than casting it to a string with "TO_CHAR()" because it will work in GROUP BY, HAVING, and with other netezza date functions. (Where as the TO_CHAR method will not)

Also, the DATE_TRUNC() function will pull a specific value out of Timestamp ('Day', 'Month, 'Year', etc..) but not more than one of these without multiple functions and concatenate.

DATE() is the perfect and simple answer to this and I am surprised to see so many misleading answers to this question on Stack. I see TO_DATE a lot, which is Oracle's function for this but will not work on Netezza.

Yiddish

Same answer three times? – Teodor