Working with Date in Oracle SQL

Topic Introduction: In this tutorial, we will discuss the use of the date in Oracle SQL.

Date In Oracle Database

The Date data is stored in the oracle database column in an internal numeric format. The stored data format includes century, year, month, day, hours, minutes, and seconds. 'DD-MON-RR' is the default display and input format for any date. Oracle's valid dates are between January 1, 4712 B.C., and December 31, 9999 A.D.

If we notice to employees' table HIRE_DATE  column & Query form Employee table return HIRE_DATE we will get data 'DD-MON-RR' in this format.

For Example 

Select Last_Name, Hire_Date

From Employees

Where employee_id=100;

The Query will return King 17-JUN-03 showing the day, month, and year. Data is not stored in this format, there is also time and century information associated with the date.

Actual Stored Data in The Database

CENTURY	YEAR	MONTH	DAY	HOUR	MINUTE	SECOND
19	87	06	17	17	10	43

RR Format and YY format

The RR format for dates bears resemblance to the YY format, although it allows for the specification of different centuries. By utilizing the RR date format element, instead of YY, you can ensure that the century of the resultant value will vary depending on the two-digit year specified and the final two digits of the present year. This chart shows the RR and YY format behavior.

RR Date Format Result

Current Year	Specified Date	RR Format	YY Format
1990	01-JAN-90	1990	1990
1990	01-JAN-23	2023	1923
2001	01-JAN-90	1990	2090
2001	01-JAN-23	2023	2023

Description

		If the specified two-digit year is:
		0–49	50–99
If two digits of the current year are:	0–49:	The return date is in the current century	The return date is in the century before the current one
	50–99:	The return date is in the century after the current one	The return date is in the current century

Using the SYSDATE Function

SYSDATE is a date function that returns the current database server date and time.

For Example:

SELECT SYSDATE 

FROM DUAL;

Return Result: 22-MAR-23

Arithmetic with Dates

01. To add or subtract a certain number of days, weeks, months, or years to or from a date, you can use the date arithmetic operations available in most programming languages and date/time libraries.

02. If you want to find the difference between two dates, you can subtract one date from another. The resulting value will be a timedelta object representing the difference in days, seconds, and microseconds between the two dates.

03. To add a certain number of hours to a date, you can divide the number of hours by 24 and add the resulting value to the date. This will give you a new date and time that is the specified number of hours ahead of the original date and time.

Perform the following operations:

Operation	Result	Description
date + number	Date	Adds a number of days to a date
date – number	Date	Subtracts a number of days from a date
date – date	Number of days	Subtracts one date from another
date + number/24	Date	Adds a number of hours to a date

Example 1:

SELECT first_name, last_name, (hire_date+5) "Five Day Adds"

FROM employees;

It will return the joining date with five days add.

Example 2:

SELECT first_name, last_name, , (hire_date-5) "Five Day Subtracts"

FROM employees;

It will return the joining date with five days subtracted.

Example 3:

SELECT first_name, last_name, (SYSDATE-hire_date)/7  WEEKS

FROM employees;

It will return the service length as an employee in a week.