Click here to Skip to main content
13,197,548 members (44,358 online)
Click here to Skip to main content
Add your own
alternative version

Stats

15.1K views
1 bookmarked
Posted 5 May 2016

How to Insert a DateTime Value into an Oracle Table

, 17 Jul 2017
Rate this:
Please Sign up or sign in to vote.
This article explains how to insert a DATE with or without a time into an Oracle table using the INSERT statement.

Introduction

Storing dates and times inside a DATE field is a good practice to get into. But, how do you actually insert them into your table?

I've been lost a few times when trying to insert a value into a table that I think should work, but I get some kind of formatting or data type error.

So, I'll show you how to do it with an example in this article.

Our Sample Table

Let's create a sample table.

CREATE TABLE student (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
date_of_birth DATE
);

This table is only used for our example, so let's ignore the fact that it doesn't have an ID field.

How can we insert data into this table?

INSERT INTO student (first_name, last_name, date_of_birth) VALUES ('Adam', 'Jones', '12/01/2016');

This is what happens:

Error starting at line : 1 in command -
INSERT INTO student (first_name, last_name, date_of_birth) VALUES ('Adam', 'Jones', '12/01/2016')
Error report -
SQL Error: ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:   
*Action:

Now, this has happened because the date value I supplied (12/01/2016) is not valid, according to the table.

How can I get it to load?

Use the TO_DATE function.

Insert a Datetime Value Using the TO_DATE Function

The TO_DATE function converts a string value to a DATE value. It's great for inserting dates into tables.

Let's try it again, this time using the TO_DATE function.

INSERT INTO student (first_name, last_name, date_of_birth) 
VALUES ('Adam', 'Jones', TO_DATE('12/01/2016', 'DD/MM/YYYY'));

1 row inserted.

This time, it has worked.

Let's check the table.

SELECT first_name, last_name, date_of_birth
FROM student;
First NameLast NameDate of Birth
AdamJones12/JAN/16

This INSERT statement can also work if I change the format to use MM/DD/YYYY.

INSERT INTO student (first_name, last_name, date_of_birth)
VALUES ('Brad', 'Smith', TO_DATE('12/01/2016', 'MM/DD/YYYY'));

1 row inserted.

Let's check the table again.

SELECT first_name, last_name, date_of_birth
FROM student;
First NameLast NameDate of Birth
AdamJones12/JAN/16
BradSmith01/DEC/16

Insert Date and Time

Those examples were helpful, but they only showed dates. What about times?

Let's try another example, but include a time.

INSERT INTO student (first_name, last_name, date_of_birth)
VALUES ('Carrie', 'Johnson', TO_DATE('12/01/2016 14:08:25', 'MM/DD/YYYY HH24:MI:SS'));

1 row inserted.

Let's look at the table again.

SELECT first_name, last_name, date_of_birth
FROM student;
First NameLast NameDate of Birth
AdamJones12/JAN/16
BradSmith01/DEC/16
Carrie Johnson 01/DEC/16

Hang on, what happened to the time? I'm sure I included a time in the INSERT statement?

I did. But, the reason it's not showing here is because the default output format for DATE probably doesn't include time.

We can check that in the NLS_SESSION_PARAMETERS table.

SELECT *
FROM nls_session_parameters
WHERE parameter = 'NLS_DATE_FORMAT';
PARAMETERVALUE
NLS_DATE_FORMATDD/MON/RR

This means that whenever we output a DATE value, it will show in this format. The RR is a two-digit year value. MON is an abbreviated month value.

This matches with our 01/DEC/16 and 12/JAN/16 values.

So, how can we display the time?

We can do this in one of two ways.

Change the Session Date Format

We can change the date format for the session, which means it only changes temporarily and is restored when we end our session.

ALTER SESSION SET nls_date_format = 'DD/MON/YYYY hh24:mi:ss';

Session altered.

Now, let's run our query again.

SELECT first_name, last_name, date_of_birth
FROM student;
First NameLast NameDate of Birth
AdamJones12/JAN/16
BradSmith01/DEC/16
Carrie Johnson 01/DEC/16 14:08:25

We get the same rows, but the time is now showing.

There is another way to show the time component without adjusting the session.

Using TO_CHAR Function to Format Output

You can also use the TO_CHAR function to format the output, which converts a date to a varchar value.

Let's change the session back to what it was.

ALTER SESSION SET nls_date_format = 'DD/MON/RR';

Session altered.

Now, let's select from our table.

SELECT first_name, last_name, date_of_birth
FROM student;
First NameLast NameDate of Birth
AdamJones12/JAN/16
BradSmith01/DEC/16
Carrie Johnson 01/DEC/16

We can see that the output now shows the date only, and not the time.

Let's use the TO_CHAR function.

SELECT first_name, last_name, TO_CHAR(date_of_birth, 'DD/MON/YYYY HH24:MI:SS') AS date_output
FROM student;
First NameLast NameDate of Birth
AdamJones12/JAN/16
BradSmith01/DEC/16
Carrie Johnson 01/DEC/16 14:08:25

You can see the output is similar to what we saw before.

We can output it in many different ways.

SELECT first_name, last_name,
TO_CHAR(date_of_birth, 'DD MONTH YY HH:MI:SS AM') AS date_output
FROM student;
First NameLast NameDate of Birth
AdamJones12 JANUARY 16 12:00:00 AM
BradSmith01 DECEMBER 16 12:00:00 AM
Carrie Johnson 01 DECEMBER 16 02:08:25 PM

So, there you have it. You can insert DATE and DATE TIME values with the TO_DATE function. You can read the values from the table directly, or format them to include times by either altering the session parameter, or using a TO_CHAR function.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Database Star
Software Developer
Australia Australia
I'm currently working as a Software Consultant, with a background in Oracle database development. I also run a website that provides SQL and database advice to database developers. I like sharing information and advice with others, so they can improve their career and improve the way that they work.

You may also be interested in...

Comments and Discussions

 
QuestionTo Insert a Date and Time Pin
Member 128267091-Nov-16 1:56
memberMember 128267091-Nov-16 1:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171020.1 | Last Updated 17 Jul 2017
Article Copyright 2016 by Database Star
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid