A common question amongst database developers is “How do I get dates and time into and out of the database in the format my script/program/table expects?” This information is based on Oracle, however I expect much of this will apply to other databases.
About the DATE and TIMESTAMP datatypes: The DATE datatype is 7-bytes, comprised of date and time information to the precision of 1 second.
TIMESTAMP can be from 7 to 11 bytes depending on the precision specified. Timestamps can represent date and time as small as the nanosecond (.000000001 seconds) The default is to a microsecond of precision (.000001 seconds.)
Note: dual is a special table for testing and development. It’s useful for returning values (results from functions or contents of variables) not stored in tables, for instance the current date.
Let’s start with some information we can grab from the system about the current date and 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. The default in Oracle is DD-MON-YY. The default format can be changed by setting the nls_date_format parameter.
SELECT to_char(sysdate, 'MM/DD/YYYY') FROM dual;
TO_CHAR(SY
----------
09/14/2005
As seen here, the to_char function requires two parameters: a date to display, and the format you want it to be in. There are dozens of 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
There are several more options than are displayed here. Your databases documentation should have a full list. These components can be used in any order or combination. These characters, as well as spaces can be used to format dates / . – : . ;
Inserting Dates
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 we’ll 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 < sysdate;
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
Those are the highlights. Most things you'll need to do will be some type of variation on these.
A new article on performing math and adjusting Oracle dates is available.
i got a small article about date / time functions: http://devtime.blogspot.com/2005/12/oracle-datetime-functions.html
But thats the problem with SQL Developer, it’s not intuitive. Toad is well loved cos it’s got all this ‘no brainer’ functionality that makes for better productivity, not that I use it all the time, lots of work in Unx too, but when you resort to using a GUI it’s gotta be quick and dirty.
I actually think SQL Developer is more intuitive than TOAD. It’s also nice to bypass the TNSNames file.
I also do most of my work on the command line, so I can save a few hundred dollars a year on a tool I don’t use much anyway. That’s a no brainer.
I have this problem. One of the columns in my oracle table is of the type timestamp(6) with timezone. It’s generally shown in this format:
19-JUN-06 11.57.44.809750 AM -07:00
However, when I do this thru JDBC, I get this format (rs.getString()):
2006-6-19 16.37.2.598680000 -7:0
When I need to go back for my second query, I generally need to provide the last timestamp I got for comparison. But each time I give back the JDBC format, I get the error “Invalid month”.
Is there anyway to convert the JDBC format into the oracle timestamp format or make oracle understand the JDBC format?
Thx
Jian,
I’m afraid I can’t help you much with this. You may want to look for a Java development discussion board. Sorry.
Jian responded:
>>
Hey Jon,
Thanks for responding so quickly. I think I figured out a solution
for my issue. I basically have to issue this cmd for every new
session I create. Then it will make Oracle output the format that I
need..
alter session set NLS_TIMESTAMP_TZ_FORMAT = ‘YYYY-MM-DD Hh24:MI:SSXFF
TZR’;
thx
Pls tell me the procedure how could i can convert date into how many years||months||days.
suppose a company do hiring in january now we want to calculate their experience that how many years,months and days they work with company
e.g hiredate :=’01-jan-2004′
result=1 Year 7 months and 7 Days
i want result like this. is it possible from query?
Thank you. It is a Quick and Good reference. I was stuck comparing dates. By your reference, I caught my mistake in the format ‘HH:MM:SS’ field which s/b ‘HH:MI:SS AM’.
Thanks again.
Afzal
MM versus MI must be the single most common mistake with the date functions. Glad this helped.
Just a reminder that this page flippin’ rocks. Count right now as another occasion that I did not walk down the hall and bug you, but instead reread the appropriate section and answered my own question.
Great page! I would just like to point out that in the code shown on this page there are several different types of single quote used. This often happens when text editors (such as Word) think the text is in English and try to correct the punctuation, changing your SQL-compliant quotes to pairs of 69-style quotes. If you copy this code, be sure to change them back. Hope this helps out some of the 81 people daily who read this page 🙂
Great article,
Can I insert data of date type which consists of time only (without date) and later querying the data by specifying time only ? In essence, I only care about time not date.
Thx in advance,
Setya
Setya,
Most developers will accomplish this by using numbers to represent hour, minute and second. You could instead use the DATE datatype and ignore the day, month and year on your selects, but sorting will become difficult, especially since if you do not specify day, month and year on the insert Oracle will default to the first day of the current month.
Hope this helps.
hey i want to group some data by month and sort it also by month(mon format).i have problems in formatting.itz taking alphabettical order
please help with sample code
Lanka,
You should be able to make this work but you will want to group by a slightly different criteria than you sort by. It should be something like this:
select to_char(activity_date, ‘MON’) month
from accounts
group by month
order by to_char(activity_date, ‘MM’);
That should give you the three letter date in the output. By using an alias for the column we can reuse the column in the group by statement but in the order by statement we perform a separate to_char to get the month as a number so it will sort properly.
Hope this helps.
Hi Jon, thanks for your article
I’m just trying to write some code in SQL in order to find out what day of the week was a specified date, for instance, 01-01-2000. It supposed to be something very simple but I’m struggling with it the whole evening, may be you’ve got any idea? THanks in advance
I have column REQDATE of type DATE in Oracle. when i insert record it gets stored as “09/14/2005 09:08:32 PM” format.
In my Java code I get another date of same type as:
String resDate = rs.getString(“RESDATE”);
//then i format resDate as “MM/dd/yyyy hh:mi:ss” but i dont get the AM/PM part so following qry fails and returns no records.
In my Java code I have to compare the date using qry:
“select * from REQ_ENTRY where REQDATE = to_date(‘”+ resDate + “‘,’MM/DD/RR HH12:MI:SS PM’)”
Please reply …..
Hi,
Thanks for this information. Very helpful since I’ve been away from database work for a couple of years due to being laid off. So now that I am working, I am getting back into SQL and PL/SQL and appreciate the people who post this kind of information.
Scott