PL/SQL Insert Dates

When managing databases, handling date and time data accurately is important. Date fields are frequently used for storing important information such as user birth dates, product release dates, and event timelines. Oracle PL/SQL provides robust support for date operations, ensuring accurate data storage and retrieval.

This article provides information how to handle dates in Oracle PL/SQL, the syntax that needs to be followed when working with dates and best practices to ensure we are inserting date data properly. We can discuss when to insert the dates and the various formatting models and functions such as TO_DATE and SYSDATE.

Inserting Dates in PL/SQL

Then we have the DATE data type in Oracle PL/SQL in which dates are stored with attributes such as day, month, year, hour, minute, and second. While Populating dates in a table it is necessary to make sure that dates are in the format specific to the database used. That is why, to turn date strings to Oracle DATE type, we normally use the TO_DATE function for further number manipulations.

The DATE Data Type

The Oracle DATE data type stores dates and times in terms of seconds. Yes it is highly flexible since it can accept many date formats that range from dates, months, years, hours, minutes and seconds. It becomes rather helpful when it is necessary to specify not only the dates but time data of the transactions or the events.

Syntax

INSERT INTO table_name (column_name1, column_name2, date_column_name)
VALUES (value1, value2, TO_DATE('date_string', 'format_model'));

Key Terms

• table_name: The name of the table into which you are inserting data.
  
• column_name1, column_name2, date_column_name: The columns where data will be inserted, including the date column.
  
• value1, value2: The values for the non-date columns.
  
• TO_DATE('date_string', 'format_model'): This function is used to convert the string representation of the date into a valid Oracle DATE format. The format_model defines how the date string should be interpreted.

Common Date Format Models

Using the TO_DATE function, we can convert date strings into Oracle’s DATE data type. Here are common date format models used:

• 'DD/MM/YYYY' – Day, month, and year.
  
• 'YYYY-MM-DD' – Year, month, and day.
  
• 'DD-MON-YYYY' – Day, month abbreviation, and year.

Examples of PL/SQL Insert Dates

Let us go through some practical examples of inserting dates into a table. Given Below are practical examples demonstrating how to add dates to a table, ensuring accurate and consistent date and time data for various use cases.

Example 1: Inserting a Date in 'DD/MM/YYYY' Format

In this example, we insert a joining date for an employee using the 'DD/MM/YYYY' format. We’ll use the employee_details table with columns employee_id, employee_name, and joining_date.

Query:

CREATE TABLE employee_details (
    employee_id NUMBER(5),
    employee_name VARCHAR2(50),
    joining_date DATE
);

INSERT INTO employee_details (employee_id, employee_name, joining_date)
VALUES (101, 'John Doe', TO_DATE('08/10/2024', 'DD/MM/YYYY'));

Output

Explanation:

In this case, the TO_DATE function is used to convert the string '08/10/2024' into a valid DATE value. The format 'DD/MM/YYYY' indicates that the day comes first, followed by the month and year.

Example 2: Inserting a Date and Time in 'DD-MON-YYYY HH24:MI' Format

Next, let’s insert a date along with a time using the 'DD-MON-YYYY HH24:MI ' format. To add both date and time, use a model that includes hours, minutes, and seconds. This example inserts a timestamp for an employee record:

Query:

INSERT INTO employee_details (employee_id, employee_name, joining_date)
VALUES (102, 'Jane Smith', TO_DATE('10-OCT-2024 15:30:00', 'DD-MON-YYYY HH24:MI:SS'));

Output

Explanation:

Here, the TO_DATE function converts the string '10-OCT-2024 15:30:00' into a valid DATE with both date and time.

Example 3: Using SYSDATE to Insert the Current Date and Time

For scenarios where the current date and time are needed, use Oracle's SYSDATE function. It returns the system date and time.

Query:

INSERT INTO employee_details (employee_id, employee_name, joining_date)
VALUES (103, 'Robert Brown', SYSDATE);

Output

Explanation:

The SYSDATE function is ideal for logging the timestamp of record entries, especially in applications like auditing and tracking.

Conclusion

Oracle PL/SQL Insert Dates is flexible, providing tools to ensure dates and times are correctly stored and retrieved. By using TO_DATE for string-to-date conversions and SYSDATE for current timestamps, we can keep our date data organized and reliable. Understanding Oracle's DATE data type and format models is key to managing time-sensitive data efficiently.