Oracle 10 Gb functions-date functions

Sysdate[Function]: returns the current date. [Parameter]: No parameter, no brackets [Return]: Date [Example] select sysdate hz from dual; return:

Add_months (d1, n1)[Function]: return the new date after the date d1 plus the n1 month. [Parameter]: d1, date, and n1 numeric [Return]: Date [Example] select sysdate, add_months (sysdate, 3) hz from dual; return: 2008-11-5, 2009-2-5

Last_day (d1)[Function]: return the date of the last day of the month in which the date d1 is located. [Parameter]: d1, date type [Return]: Date [Example] select sysdate, last_day (sysdate) hz from dual; Return: 2008-11-5,-11-30

Months_between (d1, d2)[Function]: return the number of months from date d1 to date d2. [Parameter]: d1, d2 date type [Return]: number if d1> d2, return a positive number. If d1 <d2, return a negative number. [Example] select sysdate, months_between (sysdate, to_date ('2017-01-01 ', 'yyyy-MM-DD'), months_between (sysdate, to_date ('2017-01-01 ', 'yyyy-MM-DD') from dual; return:, 34.16,-85.84

NEW_TIME (dt1, c1, c2)[Function]: Date and time of the c2 time zone corresponding to the time dt1 in the c1 time zone [parameter]: dt1, d2 date type [Return]: Date and time [parameter]: c1, the time zone corresponding to c2 and Its abbreviation Atlantic Standard Time: AST or ADT Alaska _ Hawaii time: HST or hdt uk time: BST or BDT us Mountain Time: MST or mdt us Central Time Zone: CST or CDT New World Standard Time: NST us east time: EST or EDT Pacific Standard Time: PST or PDT Greenwich Mean Time: GMT Yukou Standard Time: YST or YDT [Example] select to_char (sysdate, 'yyyy. mm. dd hh24: mi: ss') bj_time, to_char (new_time (sysdate, 'pdt ', 'gmt'), 'yyyy. mm. dd hh24: mi: ss ') los_angles from dual; Return: BJ_TIME LOS_ANGLES ------------------- interval 2008.11.05 20:11:58 2008.11.06 03:11:58 [Example] select sysdate bj_time, new_time (sysdate, 'pdt ', 'gmt') los_angles from dual; Return: BJ_TIME LOS_ANGLES ------------------- --------------------- 20:11:58 03:11:58

Round (d1 [, c1])[Function]: returns the date of the first day of the period after the date d1 is rounded to the period (c1) after rounding (similar to the value rounding). [parameter]: d1 date type, c1 is the benchmark type (parameter), and c1 is j by default (that is, the last 0 o'clock date). [parameter table]: The parameter table corresponding to c1: Last 0 o'clock Date: cancel the parameter c1 or j recent Sunday: day or dy or d last month Date: month, mon or mm or rm last season date: q last year date: syear, year, yyyy, yyy, yy, or y (multiple y indicates accuracy) The date of the last century: cc or scc [Return]: Date [Example] select sysdate the current date, round (sysdate) Last 0 o'clock date, round (sysdate, 'day') last Sunday, round (sysdate, 'month') last month, round (sysdate, 'q ') the earliest date of the last quarter, round (sysdate, 'Year') from dual;

Trunc (d1 [, c1])[Function]: return the first day date of the period where the date d1 is located (the parameter c1) [parameter]: d1 date type, c1 is struct type (parameter), c1 is j by default (that is, the current date) [parameter table]: parameter table corresponding to c1: Last 0: Date: cancel the latest Sunday: day, dy, or d parameter c1 or j (weekly order: day, one, two, three, 4, 5, 6) last month Date: month, mon or mm or rm latest season date: q last year date: syear, year, yyyy, yyy, yy, or y (multiple y indicates accuracy) The date of the last century: cc or scc [Return]: Date [Example] select sysdate the current date, trunc (sysdate) today's date, trunc (sysdate, 'day') Sunday of this week, trunc (sysdate, 'month') early this month, trunc (sysdate, 'q ') early Date of this season, trunc (sysdate, 'Year') from dual;

Next_day (d1 [, c1])[Function]: return the date of the date d1 in the next week, the day of the week (the parameter c1) [parameter]: d1 date type, c1 is the week type (parameter ), c1 is j (current date) by default. [parameter table]: c1 corresponds to Monday, Tuesday, Wednesday ...... Sunday [Return]: Date [Example] select sysdate date at that time, next_day (sysdate, 'monday') next week Monday, next_day (sysdate, 'tuesday') next week Tuesday, next_day (sysdate, 'wedday') Wednesday, next_day (sysdate, 'thurday') next week Thursday, next_day (sysdate, 'Friday') next week Friday, next_day (sysdate, 'saturday') next week Saturday, next_day (sysdate, 'sunday') from dual next Sunday;

Extract (c1 from d1)[Function]: In date/time d1, the value of parameter (c1) [parameter]: d1 date type (date)/timestamp type (timestamp), c1 is parameter type (parameter) [parameter table]: for the parameter table corresponding to c1, see [Return]: [Example] select extract (hour from timestamp '2017-2-16 2:38:40 ') hour, extract (minute from timestamp '2017-2-16 2:38:40 ') minutes, extract (second from timestamp '2017-2-16 2:38:40') seconds, extract (DAY from timestamp '2017-2-16 2:38:40 '), extract (MONTH from timestamp '2017-2-16 2:38:40') MONTH, extract (YEAR from timestamp '2014-2-16 2:38:40 ') YEAR from dual; select extract (YEAR from date '2014-2-16') from dual; select sysdate current date, extract (hour from timestamp sysdate) hour, extract (DAY from sysdate), extract (MONTH from sysdate) MONTH, extract (YEAR from sysdate) YEAR from dual;

Localtimestamp[Function]: return the date and time in the session [parameter]: No parameter, no brackets [Return]: Date [Example] select localtimestamp from dual; Return: 14-11-08 12.35.37.453000 AM

Current_timestamp[Function]: return the current date in the current session time zone using the timestamp with time zone data type [parameter]: No parameter, no brackets [Return]: Date [Example] select current_timestamp from dual; returned results: Am + Am-08 12.37.34.609000

Current_date[Function]: return the current date in the current session time zone [parameter]: No parameter, no brackets [Return]: Date [Example] select current_date from dual; return:

Dbtimezone[Function]: return time zone [parameter]: No parameter, no brackets [Return]: Response type [Example] select dbtimezone from dual;

SESSIONTIMEZONE[Function]: Return session time zone [parameter]: No parameter, no brackets [Return]: Response type [Example] select dbtimezone, SESSIONTIMEZONE from dual; Return: + 00:00 + 08:00

INTERVAL c1 set1[Function]: Change Date and Time Value. [parameter]: c1 is a numeric string or date and time string. set1 is a date parameter table. [parameter table]: set1. For details, refer to the example [Return ]: A value in the date and time format. When the previous plus signs are in smaller units of days or days, they can be used as numerical expressions. For example, 1 indicates 1 day, 1/24 indicates 1 hour, 1/24/60 indicates 1 minute [Example] selecttrunc (sysdate) + (interval '1' second), -- add 1 second (1/24/60/60) trunc (sysdate) + (interval '1' minute), -- add 1 minute (1/24/60) trunc (sysdate) + (interval '1' hour), -- add 1 hour (1/24) trunc (sysdate) + (INTERVAL '1' DAY), -- plus 1 DAY (1) trunc (sysdate) + (INTERVAL '1' MONTH), -- plus January trunc (sysdate) + (INTERVAL '1' YEAR), -- add 1-YEAR trunc (sysdate) + (interval '01: 02: 03 'hour to second ), -- add the specified hour to second trunc (sysdate) + (interval '01: 02 'minute to second), -- add the specified minute to second trunc (sysdate) + (interval '01: 02 'hour to minute), -- add the specified hour to minute trunc (sysdate) + (interval '2 'Day to minute) -- add the specified day to minute from dual;