Oracle and Dates

Something i usually suffer is on how to format date on Oracle. Here goes some tips on fomatting, inserting and comparing oracle dates:

The following brings the current date/time:

SELECT sysdate FROM dual;

SYSDATE
———
14-SEP-05

This simple select statement returns the date in the standard format (typically DD-MON-YY.)

SELECT systimestamp FROM DUAL;

SYSTIMESTAMP
—————————————————————————
14-SEP-05 04.06.31.264201 PM -04:00

Here we see that systimestamp reports more detail than sysdate, including the offset from GMT.

Displaying Dates

The to_char function will allow you to describe how you want dates displayed and will convert them to a character string in that format. For Oracle , the default is DD-MON-YY.

SELECT to_char(sysdate, 'MM/DD/YYYY') FROM dual;

TO_CHAR(SYSDATE,'mm/DD/YYYY')
———-
09/14/2005

the to_char function requires two parameters: a date to display, and the format you want it to be in. There are several formatting options, but here are some common ones:

SELECT to_char(sysdate, 'MM/DD/YY') FROM dual;

09/14/05

SELECT to_char(sysdate, ‘MM/DD/YYYY HH:MI:SS’) FROM dual;

09/14/2005 04:09:03

SELECT to_char(sysdate, ‘DAY, MONTH DD, HH12:MI AM’) FROM dual;

WEDNESDAY, SEPTEMBER 14, 04:09 PM

SELECT to_char(sysdate, ‘YYYY BC’) FROM dual;

2005 AD

SELECT to_char(systimestamp, ‘HH24:MI:SS.FF3′) FROM dual;

16:09:24.606

Inserting Dates

The function to_date works similarly to the to_char function above. You must specify a date, typically enclosed by single quotes, then describe the format with the date components as above. To demonstrate this let's create a table we can insert some dates into.

CREATE TABLE dates
(
entry NUMBER,
entry_date DATE,
CONSTRAINT pk_dates PRIMARY KEY (entry)
);

Now a few inserts:

INSERT INTO dates (entry, entry_date)
VALUES (1, sysdate);

Inserts the current date and time to the second.

INSERT INTO dates (entry, entry_date)
VALUES (2, to_date(’09/27/05′, ‘MM/DD/YY’));

INSERT INTO dates (entry, entry_date)
VALUES (3, to_date(’10/02/2005 10:05:33 PM’, ‘MM/DD/YYYY HH:MI:SS AM’));

INSERT INTO dates (entry, entry_date)
VALUES (4, to_date(’17:01:24′, ‘HH24:MI:SS’));

INSERT INTO dates (entry, entry_date)
VALUES (5, to_date(’Monday, September 12, 2:30 PM’, ‘DAY, MONTH DD, HH:MI AM’));

COMMIT;

Now let’s take a look at the data in the dates table:

SELECT entry, to_char(entry_date, 'MM/DD/YYYY HH:MI:SS AM')
FROM dates;

1 09/14/2005 09:08:32 PM
2 09/27/2005 12:00:00 AM
3 10/02/2005 10:05:33 PM
4 09/01/2005 05:01:24 PM
5 09/12/2005 02:30:00 PM

We can see that the current date and time was entered in entry 1 down to the second.

Entry 2 contains the date we entered, but since we did not specify the time it has defaulted to midnight.

Entry 3 shows a complete timestamp exactly as we specified.

In entry 4 we see the time as we specified, but since we didn’t specify a date it has defaulted to the first of this month. I have a feeling this varies from database to database. Probably best not to rely on this.

Entry 5 shows the date and time, however since we did not specify seconds they display as :00.

Comparing Dates

Dates can be compared much like other values. To demonstrate this we’ll do some quick selects on the table we just created.

select entry, to_char(entry_date, 'MM/DD/YYYY HH:MI:SS AM')
from dates
where entry_date > to_date(’09/20/2005′, ‘MM/DD/YYYY’);

2 09/27/2005 12:00:00 AM
3 10/02/2005 10:05:33 PM

select entry, to_char(entry_date, ‘MM/DD/YYYY HH:MI:SS AM’)
from dates
where entry_date <>

1 09/14/2005 09:08:32 PM
4 09/01/2005 05:01:24 PM
5 09/12/2005 02:30:00 PM

select entry, to_char(entry_date, ‘MM/DD/YYYY HH:MI:SS AM’)
from dates
order by entry_date;

4 09/01/2005 05:01:24 PM
5 09/12/2005 02:30:00 PM
1 09/14/2005 09:08:32 PM
2 09/27/2005 12:00:00 AM
3 10/02/2005 10:05:33 PM

These are the main things you can do with dates in Oracle, if you think of anything else, please let me know.

Emerson