Fun with dates

On Oracle databases, dates are stored using 7 bytes that keep information regarding date and time, including:

1. Century
2. Year
3. Month
4. Day
5. Hour
6. Minutes
7. Seconds

So when querying dates, TO_CHAR plus the proper date format must be used. For instance:

SQL> select to_char(sysdate, 'dd/mm/yyyy') from dual;

TO_CHAR(SY
----------
10/11/2008

SYSDATE is an internal PL/SQL function that returns the current date and time, it returns a DATE type value. 'dd/mm/yyyy' is a valid date format. 'dd' means 2 digits for the day number, '/' means the date part separator (other value could be '-', for instance), 'mm' means 2 digits for the month number (starting with 1 for January), and 'yyyy' means 4 digits for the year number.

Date formats can be used to take parts of the date. For instance, if you are only interested on the current month number, you use:

SQL> select to_char(sysdate, 'mm') from dual;

TO
--
11

One interesting example of date conversions is how to get the month name out of a date. The first approach (the 'beginners' approach) is here:

SQL> select DECODE (TO_CHAR (SYSDATE, 'mm'),
2 '01', 'January',
3 '02', 'February',
4 '03', 'March',
5 '04', 'April',
6 '05', 'May',
7 '06', 'June',
8 '07', 'July',
9 '08', 'August',
10 '09', 'September',
11 '10', 'October',
12 '11', 'November',
13 '12', 'December')
14 from dual;

DECODE(TO
---------
November

There's a better way to do this, and that's using TO_CHAR together with the "MONTH" date format. This format returns the month name. Here's a better approach:

SQL> select TO_CHAR (SYSDATE, 'Month') from dual;

TO_CHAR(S
---------
November

Moreover, you can use the optional "PARAMNLS" TO_CHAR parameter to get the month name in other languages. For instance, the month name in spanish:

SQL> select TO_CHAR (SYSDATE, 'Month', 'NLS_DATE_LANGUAGE = ''SPANISH''') from
dual;

TO_CHAR(SY
----------
Noviembre