Date functions in oracle sql

Many times we need to retrieve the data in different date formats. To achieve this, there are different date functions in Oracle.
Mainly we can use to_date and to_char functions associated with date and time in oracle to manipulate the date-time format data.

For example :
1. select b from x;
gives something like this

B
---------
01-APR-98

2. SELECT TO_CHAR(b, 'YYYY/MM/DD') AS b FROM x;
gives something like this

B
-------------
1998/04/01

3. select * from x where b between to_date('23-JAN-2009 00:00:01','DD-MON-YYYY HH24:MI:SS') and to_date('24-JAN-2009 23:59:59','DD-MON-YYYY HH24:MI:SS');

This will give you the all the records where date is from start of 23-Jan to end of 24th-Jan.

The general usage of to_date() and to_char() is
TO_DATE(, '')
TO_CHAR(, '')

where the '' can have more than 40 options, popular ones are listed below

MM : Numeric month (e.g., 07)
MON : Abbreviated month name (e.g., JUL)
MONTH : Full month name (e.g., JULY)
DD : Day of month (e.g., 24)
DY : Abbreviated name of day (e.g., FRI)
YYYY : 4-digit year (e.g., 1998)
YY : Last 2 digits of the year (e.g., 98)
RR : Like YY, but the two digits are ``rounded'' to a year in the range
1950 to 2049. Thus, 06 is considered 2006 instead of 1906
AM (or PM) Meridian indicator
HH : Hour of day (1-12)
HH24 : Hour of day (0-23)
MI : Minute (0-59)
SS : Second (0-59)

In the following sql we are taking the record count per day from table_name

select to_char(myDate,'DD:MM:YY HH24'), count(*)
from table_name where myDate > to_date ('11-JUN-2009','DD-MON-YYYY') group by to_char(mydate,'DD:MM:YY HH24');

There are many other functions for date-time formats, you can the usage at http://www.oradev.com/oracle_date_functions.jsp this place.