Here is few simple SQL date formatting examples that can be useful while displaying dates in different formats.There are a number of formatting techniques available for dates and timestamps.
1. DD:MONTH:YYYY HH24:MI:SS format

SQL> select distinct to_char(sysdate,'DD:MONTH:YYYY hh12:MI:ss') TIME_NOW from tab;

TIME_NOW
28:OCTOBER  :2008 04:37:12

2. Year in strings

SQ> select distinct to_char(sysdate,'DD-MONTH-YEAR hh12:mm:ss') today from tab;
TODAY
28-OCTOBER  -TWO THOUSAND EIGHT 04:10:40

3. Day and date as string

SQL> select distinct to_char(sysdate,'DAY DD--MONTH--YEAR hh12:mm:ss') today from tab;
TODAY
TUESDAY   28--OCTOBER  --TWO THOUSAND EIGHT 04:10:09

Related articles on oracle DATE , TIMESTAMP etc

• Oracle timestamp format milliseconds| Oracle Date format

• Oracle PL/SQL: To_Timestamp Function | Datetime Format Models
• Oracle DATE vs TIMESTAMP |Comparing Oracle DATE and TIMESTAMP Datatypes
• Oracle to_char function and usage
• Using oracle date format |Using the DATE Format Mask in pl/sql
• Oracle comparing dates : dates, oracle, date comparison in oracle pl/sql
• ORA-01882: TIMEZONE REGION NOT FOUND |Oracle Error
• PL/SQL EXTRACT function for oracle datetime
• Oracle Dates and Times |SYSDATE|CURRENT_TIMESTAMP |SYSTIMESTAMP |DBTIMEZONE |LOCALTIMESTAMP
• oracle to_date | Oracle/PLSQL: To_Date Function
• oracle date difference | date difference in sql and PL/SQL
• DATE Data type in Oracle|Oracle Dates and Times|Dates in Oracle
• Oracle Max(Date) function usage-SQL max date