Date Functions

One of the ORACLE's more unusual strength is its ability to store and calculate dates, and the number of seconds, minutes, hours, days, months, and years between dates.

Date Arithmetic

DATE is an ORACLE data type, just as CHAR and NUMBER are, and it has its own unique properties. The DATE data type is stored in a special internal ORACLE format that includes not just the month, day, and year, but also the hour, minute, and second.

SQL*PLUS and SQL recognize columns that are of the DATE data type, and understand that instructions from you to do arithmetic with them call for date arithmetic, not regular mathematics.

Displaying Dates

The usual handling techniques is to assign each date a serial number, which the program uses internally. What you see on the screen is the representation of that number, translated from a form the computer can understand to a form you can understand.

When ORACLE makes that transition, it uses the standard format DD MM YY Thus the date looks like this: 27 APR 97. To display the joining date of employee in the standard format, enter.

SQL > SELECT ENAME, JOIN_DT FROM EMP

2 WHERE DEPTNO = 2;

You can specify a different format for any date column by converting the date to a char value with the TO CHAR function. The syntax of the TO CHAR function is TO CHAR(dl,fmt)
Date Functions

ADD MONTHS(date,count) adds count months to date

GREATEST(datel, date2, date3,...) picks latest date from list of dates

LEAST date1, date2, ...) picks earliest date from list of dates

LAST DAY(date) gives date of last day of month that date is in

MONTHS_BETWEEN(date2, date1) give date2 date1 in months

NEXT DAY(date,'day') gives date of next day after date where 'day' is 'Monday', 'Tuesday', and so on.

NEW TIME(date,'this "other') gives the date (and time) in this time zone. This will be replaced by a
three latter abbreviation for the current time zone.

Time zones are as follows:

AST/ADT - Atlantic standard/daylight time

BST/BDT - Bering standard/daylight time

CST/CDT - Central standard/daylight time

ESVEDT - Eastern standard/daylight time

GMT - Greenwich mean time

HST/HDT - Alaska Hawaii Standard/daylight time

MST/MDT - Mountain Standard/daylight time

NST - Newfoundland standard time

PST/PDT - Pacific standard/daylight time.

YST/YDT - Yukon standard/daylight time

Domain Name Search