PL and SQL date time type functions and operations

  • 2020-05-27 07:26:14
  • OfStack

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.

Related articles: