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:

http://www.dba-oracle.com/t_nls_date_format_sysdate.htm

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