Conversion of a Date to Character sub-query in Oracle

Question

i have a sub-query returning dates this way:

SELECT DISTINCT DT_EMISSAO_NF FROM DANFE ORDER BY DT_EMISSAO_NF DESC

And, starting from this query, I need all dates returned to be converted to character in this pattern 'DD/MM/YYY'

• If I make the conversion in a single query, the ordination classifies all the numbers of the days, after the month, and then after the year, and in case I need to return as if it were the same date, the most current days returning first.

Answer 1

Just use the TO_CHAR passing the format you want.

Example:

SELECT TO_CHAR(DT_EMISSAO_NF, 'DD/MM/YYYY HH24:mi:ss') 
FROM DANFE ORDER BY DT_EMISSAO_NF DESC

To use with Distinct make a sub select, like this:

SELECT TO_CHAR(QRY.DT_CADASTRO, 'DD/MM/YYYY HH24:mi:ss') FROM 
   (SELECT DISTINCT DT_CADASTRO FROM PROJETO ORDER BY DT_CADASTRO DESC
) QRY;

More information here.