Extracting date and time from oracle

Question

I have a table in which there is a field last_update_date in which the date is displayed in short form like 25/02/2015.If I want to extract the date in long date time format like dd/mm/yyyy hh:mm:ss how can I do so.Please help on how can I query the data from this table by using to_date or extract function.

Answer 1

If you want the value as a DATE data type then use:

SELECT last_update_date FROM table_name

If the client application is not showing the time component then you need to modify the formatting preferences of the client application; you should NOT need to modify the query.

For example, SQL*Plus and SQL Developer both use the NLS_DATE_FORMAT session parameter as the default format for displaying dates and that can be modified using:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

Other client applications are going to have different ways of setting their formatting and you will need to consult the appropriate manuals/guides for those applications.

---

If you want the value as a formatted string (and not as a DATE) then use TO_CHAR:

SELECT TO_CHAR(last_update_date, 'DD/MM/YYYY HH24:MI:SS') AS formatted_date
FROM   table_name