What are the Date Types in Oracle?

Date Types in Oracle

Oracle has strong date-formatting, date-calculation, and storage features. Dates contain special features and are a basic data type, like VARCHAR2 and NUMBER. In addition to the month, day, and year, the DATE datatype also records the hour, minute, and second. Dates are stored with fractional seconds, down to the billionth of a second, in the TIMESTAMP datatypes for more detailed time tracking. Knowing these datatypes is essential for precise computations and reliable reporting since using the wrong datatype such as VARCHAR2 for date data can result in inconsistent results and needless conversion operations.

SYSDATE (Current System Date)

To get the current date and time from the operating system where the database is located, utilise the SYSDATE function. For applications that need to timestamp events or make reference to the present instant, it is an essential capability. The DATE datatype value is returned by the SYSDATE function.

Here’s an example of using SYSDATE with the DUAL table, a small Oracle table useful for testing functions or quick calculations:

SELECT SYSDATE FROM DUAL;

Output:

SYSDATE
---------
23-MAR-04

TO_CHAR (Format Dates to Strings)

Depending on a predetermined format, an Oracle DATE or TIMESTAMP value can be transformed into a human-readable character string using the TO_CHAR function. Because Oracle’s DATE datatype has an internal format, TO_CHAR lets you display it in almost any way you can think of, which makes it especially helpful.

The general format is TO_CHAR(date[, 'format_mask'[, 'NLSparameters']]).

• date must be a column defined as a DATE datatype in Oracle.
• format_mask is a collection of many options that can be combined. Common format elements include:
  • DD: Day of the month (1-31).
  • MM: Month (01-12).
  • YY or YYYY: Last two or four digits of the year.
  • MON or MONTH: Abbreviated or full name of the month.
  • DAY or DY: Full or abbreviated name of the day of the week.
  • HH or HH24: Hour of the day (1-12 or 0-23).
  • MI: Minute (0-59).
  • SS: Second (0-59).
  • FF[1..9]: Fractional seconds (for TIMESTAMP datatypes).
• NLSparameters sets the NLS_DATE_LANGUAGE option to a specific language.

When using TO_CHAR for time formatting, a typical mistake is to mix together “MM” (Month) and “MI” (Minutes). Because Oracle is so flexible, it cannot stop this problem, thus always verify your format string twice. It is also possible to suppress leading and trailing blanks or zeros in the output by using the FM (fill mode) modification.

Let’s illustrate TO_CHAR with an example:

-- Create a table for demonstration
CREATE TABLE BIRTHDAY (
    FirstName VARCHAR2(15),
    LastName VARCHAR2(15),
    BirthDate DATE
);
-- Insert some values
INSERT INTO BIRTHDAY (FirstName, LastName, BirthDate) VALUES ('VICTORIA', 'LYNN', TO_DATE('1949-05-20 03:27:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO BIRTHDAY (FirstName, LastName, BirthDate) VALUES ('GEORGE', 'SAND', TO_DATE('1946-05-12 09:30:00', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;
-- Use TO_CHAR to format the BirthDate
COLUMN Formatted FORMAT A40 WORD_WRAPPED
SELECT
    FirstName,
    BirthDate,
    TO_CHAR(BirthDate, '"Baby Girl on" fmMonth ddth, YYYY, "at" HH:MI A.M.') AS Formatted
FROM
    BIRTHDAY
WHERE
    FirstName = 'VICTORIA';

Output:

FIRSTNAME       BIRTHDATE FORMATTED
--------------- --------- ----------------------------------------
VICTORIA        20-MAY-49 Baby Girl on May 20th, 1949, at 3:27 A.M.

The literal strings "Baby Girl on" and "at" are included in the output in this example. While ddth adds an ordinal suffix such as ‘TH’, fmMonth eliminates padding from the month name.

TO_DATE (Convert Strings to Dates)

The TO_DATE function does the exact opposite of TO_CHAR in that it creates an Oracle DATE datatype from a character string (or occasionally an integer). When date information is stored as text and you need to do date arithmetic or comparisons, this conversion is crucial.

The general format is TO_DATE(string[, 'format_mask'[, 'NLSparameters']]).

• The character literal, integer, or column that contains the date information is called a string.
• The format of the input string is specified by format_mask. The format of the string being converted must be almost identical.
• NLSparameters specifies the language for month/day names, much like TO_CHAR does.

It is imperative that the format mask supplied to TO_DATE precisely matches the input string’s format. The default date format, which is typically ‘DD-MON-YY’ unless modified by NLS_DATE_FORMAT, is what Oracle will assume if the format_mask is left off. There will be an error if the format of the string is not the same as the mask or the default.

The following are restrictions for TO_DATE:

1. In the format mask, literal strings (such as “Baby Girl on the”) are prohibited.
2. Days have to be numbers; they can’t be defined.
3. While it is ignored in the string, punctuation is allowed in the format mask.

It is strongly advised to use TO_DATE for explicit conversions in order to prevent problems in the event that the default NLS_DATE_FORMAT changes.

Here’s an example:

-- Insert values using TO_DATE to ensure correct format conversion
INSERT INTO BIRTHDAY (FirstName, LastName, BirthDate) VALUES ('ALICIA', 'ANN', TO_DATE('21-NOV-1939', 'DD-MON-YYYY'));
COMMIT;
-- Verify the inserted date format using TO_CHAR
SELECT TO_CHAR(BirthDate, 'DD-MON-YYYY') AS Bday FROM BIRTHDAY WHERE FirstName = 'ALICIA';

Output:

BDAY
-----------
21-NOV-1939

The string ’21-NOV-1939′ is explicitly converted into a DATE value using the given format mask by TO_DATE, as shown here. Oracle may use the current century by default if the century is not specified explicitly in the TO_DATE function, which could result in dates that are off.

In WHERE clauses, TO_DATE is also essential for comparing date literals. For example, unless TO_DATE transforms date literals to actual dates, the LEAST and GREATEST functions accept them as strings and produce inaccurate results.

-- Incorrect comparison without TO_DATE
SELECT LEAST('20-JAN-04','20-DEC-04') FROM DUAL;
-- Correct comparison with TO_DATE
SELECT LEAST(TO_DATE('20-JAN-04', 'DD-MON-YY'), TO_DATE('20-DEC-04', 'DD-MON-YY')) FROM DUAL;

Output:

LEAST('20
---------
20-DEC-04
LEAST(TO_
---------
20-JAN-04

Since the first query compares the strings alphabetically, it gives ’20-DEC-04′, which is inaccurate; the second query, which uses TO_DATE, correctly detects ’20-JAN-04′ as the earlier date.

Date Arithmetic (Calculating Differences between Dates)

The DATE datatype in Oracle supports arithmetic operations.

1. Adding/Subtracting numbers from dates: Dates can have integers added or subtracted. For example, adding 1 to a DATE value yields the following day. One less day equals the day before. For example, 4/24 represents four hours, 10/1440 represents ten minutes, and 7/86400 represents seven seconds. Fractions can be used to represent hours, minutes, and seconds.
2. Subtracting one date from another: When one date is subtracted from another, a number that represents the number of days that have passed between the two dates is returned. The outcome may be a fraction since Oracle records the hours, minutes, and seconds along with the dates.

To illustrate date arithmetic and calculating differences like DaysOut, let’s create a BOOKSHELF_CHECKOUT table and insert some data:

-- Create BOOKSHELF_CHECKOUT table
CREATE TABLE BOOKSHELF_CHECKOUT (
    Name VARCHAR2(20),
    Title VARCHAR2(50),
    CheckoutDate DATE,
    ReturnedDate DATE
);
-- Insert values
INSERT INTO BOOKSHELF_CHECKOUT (Name, Title, CheckoutDate, ReturnedDate) VALUES ('DORAH TALBOT', 'MY LEDGER', TO_DATE('15-FEB-2002', 'DD-MON-YYYY'), TO_DATE('03-MAR-2002', 'DD-MON-YYYY'));
INSERT INTO BOOKSHELF_CHECKOUT (Name, Title, CheckoutDate, ReturnedDate) VALUES ('EMILY TALBOT', 'ANNE OF GREEN GABLES', TO_DATE('02-JAN-2002', 'DD-MON-YYYY'), TO_DATE('20-JAN-2002', 'DD-MON-YYYY'));
INSERT INTO BOOKSHELF_CHECKOUT (Name, Title, CheckoutDate, ReturnedDate) VALUES ('FRED FULLER', 'JOHN ADAMS', TO_DATE('02-FEB-2002', 'DD-MON-YYYY'), TO_DATE('01-MAR-2002', 'DD-MON-YYYY'));
INSERT INTO BOOKSHELF_CHECKOUT (Name, Title, CheckoutDate, ReturnedDate) VALUES ('FRED FULLER', 'TRUMAN', TO_DATE('15-FEB-2002', 'DD-MON-YYYY'), TO_DATE('20-MAR-2002', 'DD-MON-YYYY'));
COMMIT;
-- Calculate DaysOut for each book
SELECT
    Name,
    Title,
    CheckoutDate,
    ReturnedDate,
    ReturnedDate - CheckoutDate AS DaysOut /*Count Days*/
FROM
    BOOKSHELF_CHECKOUT
ORDER BY
    Name, CheckoutDate;

Output:

NAME                 TITLE                CHECKOUTD RETURNEDD    DAYSOUT
-------------------- -------------------- --------- --------- ----------
DORAH TALBOT         MY LEDGER            15-FEB-02 03-MAR-02         16
EMILY TALBOT         ANNE OF GREEN GABLES 02-JAN-02 20-JAN-02         18
FRED FULLER          JOHN ADAMS           02-FEB-02 01-MAR-02         27
FRED FULLER          TRUMAN               15-FEB-02 20-MAR-02

The difference in days is computed directly in this query by ReturnedDate - CheckoutDate. DaysOut, an alias, is then assigned to this computed column, enabling additional formatting or computations.

The ROUND and TRUNC date functions are provided by Oracle to handle fractional days that arise from date subtraction (because of time components).

• ROUND(date, ‘format’): The function ROUND(date, 'format') rounds a date to the closest unit that the format model specifies. Rounding to 12 A.M., it lacks a format. of the day that is closest (rounds up if it is noon or later).
• TRUNC(date, ‘format’): The function TRUNC(date, 'format') converts a date to the unit that the format model specifies. The time is set to 12 A.M. without any format. it was midnight on that day.

Normalising dates to the start of a day or year for consistent comparisons is made easier with the help of these functions. For instance, the time component is eliminated by TRUNC(SYSDATE), which sets it to midnight on the current day.

A fractional number may be used to represent the difference between two dates in months, as determined by the MONTHS_BETWEEN(date2, date1) function. It’s useful for figuring out ages or durations in terms of years and months.

-- Calculate the number of months between two dates
SELECT MONTHS_BETWEEN(TO_DATE('16-MAY-2004', 'DD-MON-YYYY'), TO_DATE('01-NOV-2003', 'DD-MON-YYYY')) AS MonthsDiff FROM DUAL;

Output:

MONTHSDIFF
----------
6.51612903

In instance, if the difference spans half months, this demonstrates that MONTHS_BETWEEN might yield fractional months. You might use FLOOR(MONTHS_BETWEEN(SysDate, Birthdate)/12) for generations of complete years.

All things considered, Oracle’s date functions offer a complete toolkit for efficiently handling temporal data, from obtaining the current time to performing intricate computations and accurate formatting.