How to convert date in Netezza to yyyymmdd from timestamp format

Question

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

Answer 1

Use the queries below to convert to a 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 a built-in function for this, 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 only return part of the timetamp date and is much more useful than casting it to a string using "TO_CHAR ()", because it will work in GROUP BY, HAVING and other netezza date functions. (Where, as the TO_CHAR method, will not)

In addition, the DATE_TRUNC () function pulls a specific value from the timestamp ("Day", "Month", "Year", etc.), but not more than one of them without several functions and concatenation.

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 a function for Oracle, but will not work on Netezza.