CODEWITHSUNDEEP
sqloracle-databasedaterounding
user7237460
user7237460

Reputation:

Formatting a rounded date in Oracle

I want to display the current date including 24 hour format, and then show two other columns with a rounded day and a rounded year also including the 24 hour format rounded to midnight. How can I format the rounded dates? I have tried this but it just shows the date in DD-MONTH-YEAR

SELECT to_char(CURRENT_DATE, 'DD-MON-YYYY HH24:MI:SS') AS "Now", ROUND(TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH24:MI:SS'),'DAY')
"Round Date", ROUND(TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH24:MI:SS'),'YEAR') "New Year"
FROM DUAL;

I have also tried with trunc but get similar results.

Thank you.

Upvotes: 1

Views: 897

Answers (1)

Dave CK
Dave CK

Reputation: 263

Something like this

SELECT to_char(current_date, 'DD-MON-YYYY HH24:MI:SS') as "Now", 
       to_char(round(current_date),'DAY'), 'DD-MON-YYYY') "Round Date", 
       to_char(round(current_date),'YEAR'), 'YYYY') "New Year"
  FROM DUAL;

Applying whatever formatting you want to each of the dates (I did just up to the precision of the rounding but that might not be what you were after)

Upvotes: 1

Related Questions