10

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

peterh
  • 11,875
  • 18
  • 85
  • 108
Ntiyiso Mbhalati
  • 109
  • 1
  • 1
  • 4

2 Answers2

18

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')
JJJ
  • 32,902
  • 20
  • 89
  • 102
user447151
  • 241
  • 3
  • 4
  • 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! – Doug_Ivison Nov 19 '21 at 12:50
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.

thagraybush
  • 131
  • 1
  • 6