EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PL/SQL Date Functions

Home » Data Science » Data Science Tutorials » Database Management Tutorial » PL/SQL Date Functions

PL_SQL Date Functions

Introduction to PL/SQL Date Functions

PL/SQL provides a lot of date functions in order to perform the date and time tasks according to the specific requirements. PL/SQL provides the various data types related to datetime and interval. These date functions make the task quite easy as the user does not need to write the length codes in order to perform the operations like calculating days in between, getting the first day. Last day of the month, rounding off the date, calculating time of the different timezone, etc. These tasks can be easily done using the simple function provided by PL/SQL by passing the required arguments.

Various PL/SQL Date Functions

Given below are the various date functions provided by PL/SQL in order to perform various operations:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

1. ADD_MONTHS(date, x): PL/SQL allows to perform the arithmetic operations on the date values. This is one of the most useful functions of date in PL/SQL which is used to add or subtract the number of the months from the given date. Here ‘x’ in the argument list is the number of months that would be added or subtracted from the given ‘date’ argument. So ‘x’ can be both negative as well as positive.

2. EXTRACT(): EXTRACT() is a very simple function used to perform the operation in PL/SQL. It basically extracts separate Year, Month, Day values from the date value.

3. LAST_DAY(date): As the name indicates, this function is used to retrieve the last date of the month for the specified ‘date’ argument. Since in a year, the number of days varies from month to month, this function is quite useful for this purpose as it helps to get the last date instantly.

4. NEXT_DAY(date, weekday): This function returns the date of the first day which will fall for the specified ‘weekday’ argument after the specified date. It takes 2 arguments and both are mandatory. For example, there is some date (‘19- JUN- 2021’, ‘SUNDAY’), so passing this argument in the above function will return the date at which the next SUNDAY will fall.

Popular Course in this category
Sale
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,891 ratings)
Course Price

View Course

Related Courses
PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

5. MONTHS_BETWEEN(date1, date2): This function is used to retrieve the months between the two dates specified by the user in the argument as ‘date1’ and ‘date2’.

There are some important points regarding the MONTHS_BETWEEN(date1, date2) functions:

  • If date1 comes after date2, then the value returned is a positive value.
  • If date1 comes before date2, then the value returned is a negative value.
  • If date1 and date2 fall in the same month, the function will return a fraction value (between -1 and +1).
  • Function returns the whole number if both the date1 and date2 falls on the last day of their respective months.

6. SYSDATE(): This function is used widely to get the system’s current date and time stored in the database. It takes no arguments. Its time component is used to extract the current time.

7. NEW_TIME (date, zone1, zone2): This function of PL/SQL is used to convert the date and it’s time components from one timezone to another timezone. The specifications of the time zones provided in the above function are not case- sensitive. This function is quite useful because one can know the exact date and time of another region providing the date and time components of its own region.

8. ROUND(x [, format_mask]): The round function of date in PL/SQL works similar to the one works in normal mathematics. It simply rounds the date values to the nearest date according to the specified format mask. It rounds the time component of the date to 12:00 A.M. Format_mask is an Optional parameter, if not provided, this function will round the date value to the nearest day (by checking the time components). The format_mask used in the round function is quite different from the mask used in TO_CHAR and TO_DATE function. PL/SQL provides the complete set of the mask that can be used in ROUND functions like CC for century, Q for Quarter, YYYY for year, SYYY rounds for next year, and so on.

9. TRUNC(x [, format_mask]): This function is used to truncate the specified date value according to the format_mask provided. It basically rounds the beginning of minute, second, hour, year, month, etc. It is somewhat similar to the FLOOR function provided in PL/SQL. This function eliminates the time components from the date and is widely used when required to make comparisons in the date values. One can easily retrieve the first day of the month and first day of the year easily by using this function. The fomat_mask is an optional argument in this function, if not provided, TRUNC sets the time to 12:00 A.M. of the same day.

Examples of PL/SQL Date Functions

Given below are the examples mentioned:

Example #1

Code:

SELECT SYSDATE AS Extracted_date, EXTRACT(Month FROM SYSDATE) AS extracted_month
FROM Dual

Output:

PLSQL Date Functions 1

Example #2

Code:

SELECT SYSDATE AS CURRENT_DATE, ADD_MONTHS(SYSDATE, -4) as BEFORE_MONTHS
FROM Dual

Output:

PLSQL Date Functions 2

Example #3

Code:

SELECT SYSDATE AS current_date, LAST_DAY(SYSDATE) AS last_month_date FROM Dual

Output:

PLSQL Date Functions 3

Example #4

Code:

SELECT EXTRACT(Month FROM SYSDATE) AS current_month, LAST_DAY(SYSDATE)- SYSDATE AS days_left
FROM Dual

Output:

PLSQL Date Functions 4

Example #5

Code:

SELECT MONTHS_BETWEEN (TO_DATE ('10-03-2021', 'dd-mm-yyyy'),
TO_DATE ('09-03-2021', 'dd-mm-yyyy')) AS months_btw
FROM Dual

Output:

MONTHS_BTW

Example #6

Code:

SELECT SYSDATE AS current_date, NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday
FROM Dual

Output:

PLSQL Date Functions 6

Example #7

Code:

SELECT SYSDATE AS current_date, ROUND (SYSDATE, 'MONTH') AS round_date
from dual;

Output:

ROUND_DATE

Example #8

Code:

SELECT SYSDATE AS current_date, TRUNC (TO_DATE (SYSDATE), 'Q') AS truncated_value
from dual;

Output:

TRUNCATED_VALUE

Example #9

Code:

SELECT TO_CHAR (NEW_TIME (TO_DATE ('06192021 10:00 AM', 'MMDDYYYY
HH:MI AM'),'CST', 'hdt'),'DD Mon, YYYY HH:MI AM') AS new_time from dual;

Output:

NEW_TIME

Conclusion

Above description clearly explains the various date functions used in PL/SQL in order to perform the different operations. Every program or query has different requirements, and date/ time are frequently used in them. So, in order to perform the operations according to the program requirement, it is mandatory for a programmer to have a clear knowledge of the various functions along with their usage.

Recommended Articles

This is a guide to PL/SQL Date Functions. Here we discuss the introduction, various PL/SQL date functions and examples respectively. You may also have a look at the following articles to learn more –

  1. Triggers in PL/SQL
  2. PL/SQL Collections
  3. PL/SQL Data Types
  4. Loops in PL/SQL

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Database Management Tutorial
  • PL/SQL
    • What is PL/SQL?
    • Careers in PL/SQL
    • PLSQL procedure
    • PL/SQL Exception
    • PL/SQL LIKE
    • PL/SQL Raise Exception
    • PLSQL rowtype
    • PLSQL? bind variables
    • PL/SQL Record
    • PL/SQL WITH
    • PL/SQL bulk collect
    • PL/SQL Block Structure
    • PL/SQL else if
    • PL/SQL nvl2
    • PL/SQL Package
    • PL/SQL exists
    • PL/SQL instr
    • PL/SQL listagg
    • PL/ SQL Formatter
    • PLSQLlength
    • PL/SQL Commands
    • PL/SQL Data Types
    • CASE statement in PL/SQL
    • PL/SQL IF Statement
    • Loops in PL/SQL
    • PL/SQL Add Column
    • For Loop in PLSQL
    • PL/SQL Cursor Loop
    • PLSQL Array
    • Cursors in PL/SQL
    • PL/SQL FOR Loop Cursor
    • PL/SQL Queries
    • PL/SQL SELECT INTO
    • PL/SQL TO_CHAR
    • PL/SQL UNION
    • PL/SQL NOT EQUAL
    • PL/SQL varray
    • PL/SQL Concatenate
    • PL/SQL UPDATE
    • PL/SQL TRIM
    • PL/SQL GROUP BY
    • PL/SQL GOTO
    • PL/SQL Date Functions
    • PL/ SQL having
    • PL/SQL to_DATE
    • PL/SQL NVL
    • PLSQL format date
    • PLSQL mod
    • PLSQL round
    • PL/SQL Boolean
    • PL/SQL exit
    • PL/SQL DECODE
    • PL/SQL ROWNUM
    • PLSQL?pivot
    • PLSQL string functions
    • PL/SQL Block
    • PL/SQL Function
    • PL/SQL Unwrapper
    • PL/SQL Table
    • PL/SQL ALTER TABLE
    • PLSQL execute immediate
    • Triggers in PL/SQL
    • PL/SQL Collections
    • PL/SQL stored procedure
    • PL/SQL Anonymous Block
    • PLSQL Interview Questions
  • DataBase Management
    • Text Data Mining
    • Roles of Database Management System in Industry
    • SQL Server Database Management Tools
    • Database administrator skills
    • Database Management Systems Advantages
    • Database Testing Interview Questions
    • Data Administrator
    • Database Administrator
    • Database Management Software
    • DataStage
    • Types of Database Models
    • Types of Database
    • Hierarchical Database Model
    • Relational Database
    • Relational Database Advantages
    • Operational Database
    • What is RDBMS?
    • What is DB2?
    • Data Masking Tools
    • Database Security
    • Data Replication
    • Bitmap Indexing
    • Second Normal Form
    • Third Normal Form
    • Fourth Normal Form
    • Data Definition Language
    • Data Manipulation Language
    • Data Control Language
    • Transaction Control Language
    • Conceptual Data Model
    • Entity-Relationship Model
    • Relational Database Model
    • Sequential File Organization
    • Checkpoint in DBMS
    • Teradata Create Table
    • Centralized Database
    • Data Storage in Database
    • Thomas write Rule
    • DBA Interview Questions
    • What is JDBC?
    • jdbc hive
    • Apriori Algorithm
    • JDBC Architecture
    • JDBC Interview Questions
    • Wildcard Characters
    • Distributed Database System
    • Multidimensional Database
  • TSQL Basic
    • TSQL
    • What is T-SQL
    • T-SQL Commands
    • T-SQL String Functions
    • TSQL Interview Questions
  • MariaDB
    • MariaDB Versions
    • MariaDB?list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB?boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL
  • SQLite
    • What is SQLite
    • SQLite Commands
    • SQLite Data Types
    • SQLite COUNT
    • SQLite Boolean
    • SQLite autoincrement
    • SQLite select
    • SQLite? Bulk Insert
    • SQLite? add column
    • SQLite? concat
    • SQLite BETWEEN
    • SQLite group by
    • SQLite CASE
    • SQLite group_concat
    • SQLite array
    • SQLite? enum
    • SQLite sum
    • SQLite create table
    • SQLite Alter Table
    • SQLite Create Database
    • SQLite Delete
    • SQLite connection string
    • SQLite Database
    • SQLite Describe Table
    • SQLite Show Tables
    • SQLite exit
    • SQLite create index
    • SQLite foreign key
    • SQLite Stored Procedures
    • SQLite Extension
  • DB2
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE
    • View Serializability in DBMS
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • Dataset Normalization
    • MariaDB Max Connections
    • jdbc connection
    • MariaDB GUI
  • DBMS
    • Introduction To DBMS
    • DBMS ER Diagram
    • What is DBMS?
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • Netezza Database
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS

Related Courses

SQL Certification Course

PL/SQL Certification Course

Oracle Certification Course

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - SQL Certification Course Learn More