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 Name | Last Name | Date of Birth |
| Adam | Jones | 12/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 Name | Last Name | Date of Birth |
Adam | Jones | 12/JAN/16 |
Brad | Smith | 01/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 Name | Last Name | Date of Birth |
Adam | Jones | 12/JAN/16 |
Brad | Smith | 01/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';
| PARAMETER | VALUE |
NLS_DATE_FORMAT | DD/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 Name | Last Name | Date of Birth |
Adam | Jones | 12/JAN/16 |
Brad | Smith | 01/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 Name | Last Name | Date of Birth |
Adam | Jones | 12/JAN/16 |
Brad | Smith | 01/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 Name | Last Name | Date of Birth |
Adam | Jones | 12/JAN/16 |
Brad | Smith | 01/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 Name | Last Name | Date of Birth |
Adam | Jones | 12 JANUARY 16 12:00:00 AM |
Brad | Smith | 01 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.
