Wednesday, August 31, 2011

Oracle time/date format

Date or time in Oracle are not always displayed in the format you want.
In order to change this:


ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MON/YYYY HH24:MI:SS';
select sysdate from dual;

From then, your session will display the date in the desired format.
For more details on the format syntax:


Here's an example of select dealing with Date format, for select with from and to date


select * from OPBI.prodapp_gen_machine_stats where 
hostname = 'sew3'  and collection_timestamp between to_date('2011/10/02 08:00:00' ,'yyyy/mm/dd hh24:mi:ss') and to_date('2011/10/05 20:00:00','yyyy/mm/dd hh24:mi:ss') order by collection_timestamp


No comments: