The Oracle PL/SQL DATE data type is used to store date and time values.
Syntax
The syntax for declaring a DATE variable is as follows:
variable_name DATE;
Examples
An example of how to declare a DATE variable, assign a value, and store it in a table is as follows:
DECLARE l_date DATE; BEGIN l_date := '01-JAN-2022'; INSERT INTO mytable(mydate) VALUES (l_date); END;
In the example above, the DATE variable l_date is declared and assigned the value of ’01-JAN-2022′. This value is then inserted into the mytable table.
The format of the DATE data type is as follows:
DD-MON-YYYY
where DD is the day, MON is the month, and YYYY is the year.
You can add days to a date by using the INTERVAL. The syntax for INTERVAL is as follows:
select date + INTERVAL number_of_days DAY from dual;
For example, to add 10 days to the date ’01-JAN-2022′, you would use the following:
select to_date('01-JAN-2022') + INTERVAL '10' DAY from dual;
which would return the date ’11-JAN-2022′.
You can also store the current date and time in a DATE variable by using the SYSDATE function. For example, to store the current date and time in a DATE variable named l_date, you would use the following:
declare
l_date date;
begin
select sysdate into l_date from dual;
DBMS_OUTPUT.PUT_LINE('l_date = '||l_date);
end;
or
declare
l_date date:=sysdate;
begin
DBMS_OUTPUT.PUT_LINE('l_date = '||l_date);
end;
The SYSDATE function returns the current date and time in the following format:
DD-MON-YYYY HH24:MI:SS
where DD is the day, MON is the month, YYYY is the year, HH24 is the hour (in 24 hour format), MI is the minute, and SS is the second.
Oracle provides a number of other functions for working with dates.
