In this blog, we’ll discuss the Date and Time in PL/SQL. There are two classes of date and time related data types in PL/SQL −
• Datetime data types
• Interval data types
The Datetime data types are −
• DATE
• TIMESTAMP
• TIMESTAMP WITH zone
• TIMESTAMP WITH civil time ZONE
The Interval data types are −
• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND
Field Values for Datetime and Interval Data Types
Both datetime and interval data types contains fields. The values of those fields determine the worth of the info type. the subsequent table lists the fields and their possible values for datetimes and intervals.

Field Name	Valid Datetime Values	Valid Interval Values
YEAR	-4712 to 9999 (excluding year 0)	Any nonzero integer
MONTH	01 to 12	0 to 11
DAY	01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale)	Any nonzero integer
HOUR	00 to 23	0 to 23
MINUTE	00 to 59	0 to 59
SECOND	00 to 59.9(n), where 9(n) is the precision of time fractional seconds The 9(n) portion is not applicable for DATE.	0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
TIMEZONE_HOUR	-12 to 14 (range accommodates daylight savings time changes) Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_MINUTE	00 to 59 Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_REGION	Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_ABBR	Not applicable for DATE or TIMESTAMP.	Not applicable

The Datetime Data Types and Functions
Following are the Datetime data types −
DATE
It stores date and time information in both character and number datatypes. it’s made from information on century, year, month, date, hour, minute, and second. it’s specified as −
TIMESTAMP
It is an extension of the DATE data type. It stores the year, month, and day of the DATE datatype, alongside hour, minute, and second values. it’s useful for storing precise time values.
TIMESTAMP WITH zone
It is a variant of TIMESTAMP that has a zone region name or a zone offset in its value. The zone offset is that the difference (in hours and minutes) between civil time and UTC. This data type is beneficial for collecting and evaluating date information across geographic regions.
TIMESTAMP WITH civil time ZONE
It is another variant of TIMESTAMP that has a zone offset in its value.
Following table provides the Datetime functions (where, x has the datetime value) –

S.No	Function Name & Description
1	ADD_MONTHS(x, y); Adds y months to x.
2	LAST_DAY(x); Returns the last day of the month.
3	MONTHS_BETWEEN(x, y); Returns the number of months between x and y.
4	NEXT_DAY(x, day); Returns the datetime of the next day after x.
5	NEW_TIME; Returns the time/day value from a time zone specified by the user.
6	ROUND(x [, unit]); Rounds x.
7	SYSDATE(); Returns the current datetime.
8	TRUNC(x [, unit]); Truncates x.

Timestamp functions (where, x features a timestamp value) –

S.No	Function Name & Description
1	CURRENT_TIMESTAMP(); Returns a TIMESTAMP WITH TIME ZONE containing the present session time along with the session zone.
2	EXTRACT({ YEAR \| MONTH \| DAY \| HOUR \| MINUTE \| SECOND } \| { TIMEZONE_HOUR \| TIMEZONE_MINUTE } \| { TIMEZONE_REGION \| } TIMEZONE_ABBR ) FROM x) Extracts and returns a year, month, day, hour, minute, second, or time zone from x.
3	FROM_TZ(x, time_zone); Converts the TIMESTAMP x and the time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE.
4	LOCALTIMESTAMP(); Returns a TIMESTAMP containing the civil time within the session zone.
5	SYSTIMESTAMP(); Returns a TIMESTAMP WITH TIME ZONE containing the present database time alongside the database zone.
6	SYS_EXTRACT_UTC(x); Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP containing the date and time in UTC.
7	TO_TIMESTAMP(x, [format]); Converts the string x to a TIMESTAMP.
8	TO_TIMESTAMP_TZ(x, [format]); Converts the string x to a TIMESTAMP WITH TIMEZONE.

Examples
The following code snippets illustrate the utilization of the above functions −
Example 1
SELECT SYSDATE FROM DUAL;
Output −
08/31/2012 5:25:34 PM
Example 2
SELECT TO_CHAR(CURRENT_DATE, ‘DD-MM-YYYY HH:MI:SS’) FROM DUAL;
Output −
31-08-2012 05:26:14
Example 3
SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;
Output −
01/31/2013 5:26:31 PM
Example 4
SELECT LOCALTIMESTAMP FROM DUAL;
Output −
8/31/2012 5:26:55.347000 PM
The Interval Data Types and Functions
Following are the Interval data types −
• IINTERVAL YEAR TO MONTH − It stores a period of your time using the YEAR and MONTH datetime fields.
• INTERVAL DAY TO SECOND − It stores a period of your time in terms of days, hours, minutes, and seconds.
Interval Functions

S.No	Function Name & Description
1	NUMTODSINTERVAL(x, interval_unit); Converts the number x to an INTERVAL DAY TO SECOND.
2	NUMTOYMINTERVAL(x, interval_unit); Converts the number x to an INTERVAL YEAR TO MONTH.
3	TO_DSINTERVAL(x); Converts the string x to an INTERVAL DAY TO SECOND.
4	TO_YMINTERVAL(x); Converts the string x to an INTERVAL YEAR TO MONTH.

So, this brings us to the end of blog. This Tecklearn ‘Date and Time Handling in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

369