Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞
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;
Add_months (date,x) function that returns the value of date after X-month. X can be any integer. Returns the last day of the result month if the month of the result contains less than the daily component of the date month. If not less than, the result is the same as the daily component of date. The time component is the same.
Current_date returns the current date of the session time zone as a date type. This function is similar to sysdate except Sysdate regardless of the session time zone.
current_timestamp[(Precision)] returns the current date of the session time zone with the TIMESTAMP with TIMEZONE type. If precision is specified, it refers to the precision of the number of seconds returned, which defaults to 6.
Dbtimezone returns the time zone for the database.
Last_day (date) Specifies the date of the last day of the month in which the date is located, and this function can be used to determine how many days are still available this month.
localtimestamp[(Precision)] returns the current date of the session time zone with the timestamp type. If precision is specified, it refers to the precision of the number of seconds returned, which defaults to 6.
Months_between (Date1, previous date) the number of months (calculated as the smallest unit of day) between two dates. The number of months that the return is the difference. If the daily component of the Date1 and Date2 is the same, or if both dates are the last day of the month, the return result is an integer. Otherwise, the return result contains a fraction of one-month-31-day calculations.
New_time (d,zone1,zone2) function that returns the date and time in the time zone zone2 when the date and time in the Zone1 are d.
The return type is date. Zone1 and Zone2 are character strings, and the other time zones can be in oracle9i
Through the query v$timezone_names get.
Next_day (date, day of the week) date of day after seven days after the specified date.
ROUND (date, ' month/year ') rounded to get a new date. Reserve position is month and year
Sessiontimezone returns the time zone for the current session. The return type is the character string of a time zone offset or time zone slice name. If you specify a format, the format is the same as in the ALTER session statement.
SYS_EXTRACT_UTC (DateTime) returns the time in UTC (Coordinated universal times) from the provided DateTime.
DateTime must contain a time zone.
Systimestamp returns the current date and time with the timestamp with TIMEZONE. When used in distributed SQL statements, returns the date and time of the local database.
TRUNC (date, ' month/year ') intercept
Tz_offset (timezone) returns the offset between the provided timezone and UTC as a character string. TimeZone can be specified as a time zone name or an offset represented by the ' +/-hh:hi ' format. You can also use the Sessiontimezone and Dbtimezone functions to return a format of ' +/-hh:hi '.
Character string time zone
AST Atlantic Standard When
ADT Atlantic Daylight Time
BST Bering on time
White BDT Daylight Time
CST Central Standard When
CDT Central Daylight Saving time
EST Eastern Standard Time
EDT East Daylight Time
GMT Greenwich Mean Time
When HST Alaska Hawaiian standard
HDT Alaska Hawaii Daylight Time
MST Mountain Standard Time
MDT Mountain Daylight Saving Time
NST Newfoundland Standard Time
PST Pacific Standard When
PDT Pacific Daylight Time
YST Yukon Standard When
YDT Yukon Daylight Time
Date and date time arithmetic
Operation return type
Returns the number of days between D1 and D2. The value is a number whose fractional part represents a fraction of a day.
Returns the time interval between DT1 and DT2.
Returns the gap between I1 and I2.
Illegal-can only be subtracted between two dates.
Illegal-can only be subtracted between two dates.
Returns the I1 and I2.
Add n days on the D1 as the date type. n can be a real number, it contains a fraction of a day.
Subtract n days from the D1 as the date type. n can be a real number, it contains a fraction of a day.
Returns the DT1 and I1.
Returns the gap between DT1 and I1.
Returns the N-second side of the I1.
Returns the value of I1 divided by N.
Note in table:
D1 and D2 refer to the date value;
DT1 and DT2 refer to the date time value;
I1 and I2 refer to the time interval value;
n refers to a numeric value.
This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or
reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or
complaint, to firstname.lastname@example.org. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
and provide relevant evidence. A staff member will contact you within 5 working days.