Lesson 7	Date and Time Functions for Date Manipulation
Objective	Identify valid date-related functions and Oracle's standard date format.

Oracle Date and Time Functions

Oracle’s DATE datatype stores both the calendar date and time-of-day down to the second. Although the default display format in most Oracle environments is still influenced by the NLS_DATE_FORMAT parameter, the underlying DATE value always contains:

Year
Month
Day
Hour
Minute
Second

Oracle 23c continues to support a rich family of date-manipulation functions. These functions allow developers to add or subtract intervals, convert dates to character strings, and compute differences between dates.

The most commonly used date functions include:

Function	Syntax	Description
`ADD_MONTHS`	`ADD_MONTHS(col1, n)`	Adds `n` months to a date.
`LAST_DAY`	`LAST_DAY(col1)`	Returns the last day of the month for the given date.
`MONTHS_BETWEEN`	`MONTHS_BETWEEN(col1, col2)`	Computes the number of months between two dates, returning fractional values where applicable.
`NEW_TIME`	`NEW_TIME(col1, zone1, zone2)`	Converts a date from one time zone to another. (Still supported in Oracle 23c.)
`NEXT_DAY`	`NEXT_DAY(col1, day)`	Returns the next occurrence of the specified weekday.
`ROUND`	`ROUND(col1, round_to)`	Rounds a date to the nearest unit (day, month, year, etc.).
`TRUNC`	`TRUNC(col1, trunc_to)`	Truncates a date to a specified unit (day, month, year, etc.).
`TO_CHAR`	`TO_CHAR(col1, date_format)`	Formats a date as a character string.

Modern Date Formatting

Oracle 23c continues to support TO_CHAR for formatting dates, especially in reporting, SQL*Plus output, and application code.

TO_CHAR remains the primary formatting function.
Formatting behavior is influenced by NLS_DATE_FORMAT unless a format model is explicitly supplied.
Using a format model ensures deterministic output and is recommended in production SQL.

TO_CHAR Format Models

A format model defines how a DATE or TIMESTAMP should be displayed. Example syntax:

TO_CHAR(date_column, 'DD-Mon-YYYY HH24:MI:SS')

Common date format symbols include:

Format	Description
`MM`	Month number (01–12)
`Month`	Full month name, initial capital
`MON`	Month abbreviation (JAN–DEC)
`DD`	Day of month (01–31)
`DDD`	Day of year (1–366)
`DAY`	Full weekday name
`YYYY`	Four-digit year
`RR`	Legacy “rounding” format for two-digit year input (still supported)
`HH24`	Hour (00–23)
`MI`	Minutes
`SS`	Seconds

Date formatting using TO_CHAR with month/day names. — `SAMPLE_ONE ----------------------- 09/09/1999 08:29 p.m. SAMPLE_TWO ----------------------- 252 - 1999 08:29:06 SAMPLE_THREE ------------------------------ Thursday , September 09, '99, 73746 Input truncated to 1 characters SAMPLE_FOUR ------------------------------- 08:29 Sep. 09, Nineteen Ninety-Nine SQL>`
These examples demonstrate how `TO_CHAR` can mix text, punctuation, and date elements to produce formatted output. This behavior remains unchanged in Oracle 23c.

Date arithmetic using plus and minus operators. — `1 SELECT 2 SALES_DATE, 3 SALES_DATE+2 PLUS_TWO, 4 SALES_DATE - 10 MINUS_TEN 5* FROM CUSTOMER_SALE SQL> / SALES_DAT PLUS_TWO MINUS_TEN --------- -------- --------- 01-MAR-00 03-MAR-00 20-FEB-00 02-JAN-99 04-JAN-99 23-DEC-98 14-JUL-99 16-JUL-99 04-JUL-99 12-DEC-99 14-DEC-99 02-DEC-99 29-FEB-00 02-MAR-00 19-FEB-00 SQL>`
Adding or subtracting integers from a DATE continues to adjust the value by whole days in Oracle 23c.

Subtracting two dates to compute differences. — `1 SELECT 2 SALES_DATE, 3 SALES_DATE - TO_DATE('15-MAR-99') DIFF 4* FROM CUSTOMER_SALE SQL> / SALES_DAT DIFF --------- ---------- 01-MAR-00 352 02-JAN-99 -71.39583 14-JUL-99 121.42639 12-DEC-99 272.67708 29-FEB-00 351.62708 SQL>`
Subtracting DATE values returns the number of days (with fractional components). This behavior remains fully supported in Oracle 23c.

Using ADD_MONTHS and NEXT_DAY to compute derived dates. — `SQL> SELECT SALES_DATE, 2 ADD_MONTHS(SALES_DATE,3) THREE_MONTHS, 3 NEXT_DAY(SALES_DATE,'MONDAY') NEXT_MONDAY 4 FROM CUSTOMER_SALE 5 /`

`SALES_DAT THREE_MON NEXT_MOND --------- --------- --------- 01-MAR-00 01-JUN-00 06-MAR-00 02-JAN-99 02-APR-99 04-JAN-99 14-JUL-99 14-OCT-99 19-JUL-99 12-DEC-99 12-MAR-00 13-DEC-99 29-FEB-00 31-MAY-00 06-MAR-00 SQL>`
Both `ADD_MONTHS` and `NEXT_DAY` function exactly the same way in Oracle 23c.

Oracle SYSDATE, CURRENT_DATE, and SYSTIMESTAMP

Oracle provides multiple “current date/time” functions:

SYSDATE — Date and time from the database server’s OS.
CURRENT_DATE — Current date/time in the user’s session time zone.
SYSTIMESTAMP — Full timestamp (fractional seconds, time zone).


SELECT SYSDATE FROM DUAL;

SYSDATE
---------
28-FEB-08

All three are fully supported in Oracle 23c and are foundational in time-based logic.