1.
Introduction
1.1.
Field Values: Datetime and Interval
1.2.
The Datetime data types and functions
1.3.
The Interval data types and their functions
2.

3.

4.

5.

6.
FAQs
7.
Key Takeaways
Last Updated: Mar 27, 2024

# PL/SQL Date and Time

PRASHANT SINGH

## Introduction

PL/SQL has a date and time datatype that helps to hold and calculate dates, intervals, and times. The variable of type date or time contains a value known as Datetime, while the variable that keeps the interval data type is known as Interval. Both these data types have fields that set the value. We will study them in detail.

The following are the Datetime data types:

• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

The following are the Interval data types:

• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND

### Field Values: Datetime and Interval

The following are the field values for Datetime and Interval:

### The Datetime data types and functions

DATE

It stores the date and time information in both number and character data types. It comprises the daytime from midnight in seconds. SYSDATE is a date function that gives the present time and date. We can also apply mathematical operations like addition and subtraction on dates in PL/SQL. For example, SYSDATE-1 points to yesterday while SYSDATE+1 points to tomorrow.

TIMESTAMP

It stores the day, month, and year of the DATE data type and hour, minute, and second values. It is an extension of the data type DATE. It is used to store the precise time values.

TIMESTAMP WITH TIME ZONE

It is an extension of the TIMESTAMP data type that contains the time zone region name(or a time zone offset) in its value. The time zone offset is the difference between local time and UTC. This data type is used to collect and evaluate data information across geographic regions.

TIMESTAMP WITH LOCAL TIME ZONE

It is another extension of TIMESTAMP that includes a time zone offset in its value.

The following are the Datetime functions (where x has the DateTime value):

The following are the Timestamp functions (where x has the timestamp value):

Examples:

The following are some examples with their outputs.

Example 1

Output

It will give the current date and time.

Example 2

Output

It will give the current date and time in the given format

Example 3

Output

This will increase the number of months by 6 in the current date and time

Example 4

Output

### The Interval data types and their functions

INTERVAL YEAR TO MONTH

The INTERVAL YEAR TO MONTH data type is used to store and compute years and months intervals. Precision is used here; precision counts the number of digits in a year's field. The value precision should be any integer literal from 0 to 4. The default value is set to 2.

INTERVAL DAY TO SECOND

Interval year to a second data type is used to store and compute days, hours, minutes, and seconds intervals.

The following are the functions in Interval in PL/SQL:

Also see, DCL Commands in SQL and Tcl Commands in SQL

## FAQs

1. What is the current timestamp?
The current timestamp or CURRENT_TIMESTAMP describes a timestamp dependent on the reading of the time of the day clock while executing a SQL statement in the server.

2. What does Sysdate return in Oracle?
The SYSDATE() function gives the current date and time. It returns the value of the Date data type.

3. What is the complete form of PL/SQL?
It stands for Procedural Language extensions to SQL.

## Key Takeaways

In this blog, we learned about the data types in PL/SQL, i.e., Datetime data type and interval data type. We study each of these data types and the functions with their descriptions. We learned how to use these functions with examples to understand them better.

Visit here for the top 100 SQL problems asked in various product and service-based companies like Google, Microsoft, Infosys, IBM, etc.

You can visit Coding Ninjas Studio to practice programming problems for your complete interview preparation. Check it out to get hands-on experience with frequently asked interview questions and land your dream job.

Live masterclass