CODEWITHSUNDEEP
datetimeoracle10gtypeconverter
Varun
Varun

Reputation: 135

oracle 10g convert date column with timestamp to dd-mon-yyyy format

Hi am trying to convert column defined as date to date with format DD-MON-YYYY but can't get it working

 select 
   to_date(to_char
   (DESIGN_COMPLETION_DATE,'DD-MON-YYYY'),'DD-MON-YYYY') as Assessment_Completion_Date
    from nbi_dates

Also Tried

 Select to_date(DESIGN_COMPLETION_DATE,'DD-MON-YYYY') as Assessment_Completion_Date
    from nbi_dates

What works is, but I can't do any calculations on it as it is char

 Select to_char(DESIGN_COMPLETION_DATE,'DD-MON-YYYY') as Assessment_Completion_Date
    from nbi_dates

Thanks

Upvotes: 0

Views: 12461

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

If you have a DATE that includes a time portion but are only interested in the actual date part, you can use the trunc function:

select trunc(design_completion_date) as assessment_completion_date from nbi_dates

An example of the difference using sysdate; notice the time on the trunc'd version has been set to midnight:

SQL> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate, trunc(sysdate) from dual;

SYSDATE             TRUNC(SYSDATE)
------------------- -------------------
11/04/2013 15:14:31 11/04/2013 00:00:00

A DATE has no inherent format. DD-MON-YYYY is a format mask applied to display the date, or to convert it to a string representation, which is usually only necessary for display anyway. What you have as your third option is right for that purpose, but not if you want to do any further date calculations with the result.

Upvotes: 1

IndoKnight
IndoKnight

Reputation: 1864

Select to_date(to_char(DESIGN_COMPLETION_DATE,'DD-MON-YYYY'),'DD-MON-YYYY') as Assessment_Completion_Date
from nbi_dates

or simply (in case you want date object for calculations but not for rendereing)

    Select DESIGN_COMPLETION_DATE as Assessment_Completion_Date
from nbi_dates

Upvotes: 0

Related Questions