Internal storage format: Century, year, month, day, hour, minute, second The default format is: DD-MON-RR. SYSDATE returns the current system time. SELECT SYSDATE FROM DUAL; The mathematical operation of a date SELECT (SYSDATE-HIRE_DATE) /7 FROM TABLENAME WHERE ROWNUM; Digital column ADD_MONTHS(date,x) function, which returns the value of DATE plus the date after X. X can be any integer. If the resulting month contains less of a daily component than the DATE month, the last day of the resulting month is returned. If not, the result is the same as the daily component of DATE. The time component is the same. CURRENT_DATE returns the current date in the session time zone as DATE. This function is similar to SYSDATE except that SYSDATE doesn’t matter when the session time zone. CURRENT_TIMESTAMP[(precision)] returns the current date in the session time zone as of type TIMESTAMP WITH TIMEZONE. If you specify precision, it means the precision of the number of seconds returned, which defaults to 6. DBTIMEZONE returns the time zone of the database. LAST_DAY (date) specifies the date of the last day of the month in which the date is located. This function can be used to determine how many days remain in the month. LOCALTIMESTAMP[(precision)] returns the current date of the session time zone as TIMESTAMP. If you specify precision, it means the precision of the number of seconds returned, which defaults to 6. MONTHS_BETWEEN(date1, previous date) the number of months (measured in days) between two dates. The return is the number of months. If the daily components of date1 and date2 are the same, or if each date is the last day of the month, the return result is an integer. Otherwise, the return result contains 1 score, calculated on the basis of 1 month and 31 days. The NEW_TIME(d,zone1,zone2) function returns the date and time in zone2 when the date and time in zone1 are D. The return type is DATE. zone1 and zone2 are character strings. Additional time zones are available in ORACLE9I By querying V$TIMEZONE_NAMES. NEXT_DAY (date, day of the week) the date of one of the last 7 days to be encountered after the specified date. ROUND (date, ‘MONTH/YEAR’) 4 rounds 5 to get the new date. The reserved position is month and year SESSIONTIMEZONE returns the time zone of the current session. The return type is a character string for a time zone offset or time zone name. If you specify the format, it is the same as in the ALTER SESSION statement. SYS_EXTRACT_UTC(datetime) return time from the DATETIME provided with UTC(Coordinated Universal Time). DATETIME must contain one time zone. SYSTIMESTAMP returns the current date and time as TIMESTAMP WITH TIMEZONE. Returns the date and time of the local database when used in a distributed SQL statement. Intercept TRUNC (date, ‘MONTH/YEAR’) TZ_OFFSET(timezone) returns the offset provided between timezone and UTC as a character string. timezone can be specified as an offset in the time-zone name or ’+/ -HH :HI’ format. You can also use the SESSIONTIMEZONE and DBTIMEZONE functions, with the return format ’+/ -HH :HI’. Character string time zone AST Atlantic standard hour ADT Atlantic daylight saving time BST bering standard time BDT daylight saving time CST central standard time CDT central daylight saving time EST eastern standard time EDT eastern daylight saving time GMT Greenwich mean time HST Alaska Hawaii standard time HDT Alaska Hawaii daylight saving time MST Mountain standard time MDT Mountain summer time NST Newfoundland standard time PST Pacific standard hour PDT Pacific daylight saving time YST YuKon standard time YDT YuKon daylight saving time Date and date time arithmetic Operation return type The results of d1-d2 NUMBER Returns the number of days between D1 and D2. This value is a numeric value whose fractional part represents the fraction of a day. dt1-dt2 INTERVAL Returns the time interval between DT1 and DT2. i1-i2 INTERVAL Returns the difference between i1 and i2. d1+d2 N/A Illegal - can only subtract between two dates. dt1+dt2 N/A Illegal - can only subtract between two dates. i1+i2 INTERVAL Returns the sum of i1 and i2. d1+n DATE Add N days to D1 and return as DATE type. N can be real, and it contains fractions of a day. d1-n DATE Subtracting N days from D1 is returned as the DATE type. N can be real, and it contains fractions of a day. dt1+i1 DATETIME Returns the sum of DT1 and I1. dt1-i1 DATETIME Returns the difference between DT1 and I1. i1*n INTERVAL Return I1 to N. i1/n INTERVAL Returns the value of I1 divided by N. Note: in the table D1 and D2 refer to date values; DT1 and DT2 are date and time values; I1 and I2 refer to the time interval values; N exponent.