How do i convert date in Netezza to yyyymmdd from

Question

How do i convert date in Netezza to yyyymmdd from timestamp format

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')

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.