How to use Oracle Date-based functions

Article Source: http://www.cnblogs.com/zfc2201/p/3284965.html

Date formatting: Alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss dy '

Note: The HH24 format cannot be set to am pm on PM Indicator

sysdate"function": Returns the current date. "Parameters": no arguments, no parentheses "return": Date "Example"

Select Sysdate from dual;

return: 2008-11-5

add_months (d1,n1)"function": Returns the new date based on the date D1 plus N1 months. "Parameters": D1, Date type,N1 numeric "return": Date "Example"

return: 2008-11-5,2009-2-5

last_day (D1)"function": Returns the date D1 the last day of the month. "Parameters": D1, Date "return": Date "Example"

Select Sysdate,last_day (sysdate) from dual;

　return: 2015-04-12,2015-04-30

Months_between (D1,D2)"function": Returns the number of months between the date D1 to the D2 of the date. "Parameter": d1,d2 Date Type "return": number If d1>d2, returns positive if d1<d2, returns negative numbers "Example"

Select Sysdate,months_between (sysdate,to_date (' 20150311 ', ' YYYYMMDD ')), Months_between (to_date (' 20150311 ', ' Yyyymmdd '), sysdate) from dual;

return: 2015-04-12,1,-1

new_time (DT1,C1,C2)"function": Gives time DT1 in C1 time zone corresponding C2 time zone date and time "parameter": dt1,d2 Date Type "return": DateTime "parameter": c1,c2 corresponding time zone and its shorthand     Atlantic Standard Time: AST or ADT     Alaska _ Hawaii Time: HST or HDT     UK Daylight Time: BST or BDT     United States mountainous times: MST or MDT     US Central Time zone: CST or CDT     Newland Standard Time: NST     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
-------------------      -------------------
2015.04.12 18:13:19 2015.04.13 01:13:19

   round (D1[,c1])   function: Given date D1 by period (parameter C1) The first day of the rounded period (similar to the value rounding) "parameter": D1 date type, c1 is a character (parameter) , c1 default is J  (that is, last 0 date) "Parameter table": C1 corresponding parameter table: Last 0 Point Date: Cancel parameter C1 or J recent Sunday: day or dy or D date of month: month or Mon or mm or RM last quarter date: Q Latest beginning Date: Syear or year or yyyy or yyy or yy or Y (multiple y represents precision) Last century Date: CC or SCC "back" : Date "Example" Select Sysdate date, round (sysdate) Last 0 point date, round (Sysdate, ' Day ') In the last Sunday,  rounding by numbers, counting from Sunday to 1, then 4,5,6,7 into Sunday, and then into Sunday   round (sysdate, ' month ') At the beginning of recent month, round (sysdate, ' Q ') the latest quarter date, round (sysdate, ' year ') the latest beginning date from dual; 
 
 

   Trunc (D1[,c1])   function: Returns the first day of the date D1 period (parameter C1) Date "parameter": D1 Date Type , C1 is a character type (parameter), C1 default is J (that is, the current date) "Parameter table": C1 corresponding parameter table: Last 0 Point date: Cancel parameter C1 or J recent Sunday: day or Dy or D (Weekly order: Days, one, two, three, four, five, Last month Date: Month or Mon or mm or RM recent season date: Q Latest beginning Date: Syear or year or yyyy or yyy or yy or y (multiple y for precision) Last century Date: CC or SCC "return": Date "Example" Select Sysdate Span style= "color: #ff0000;" > at the time  date, trunc (sysdate)  trunc (sysdate, ' Day ')  this week in Sunday , from Sunday counted the first day trunc (sysdate, ' month ') earlier this month, trunc (sysdate, ' Q ') the beginning of the season, Trunc (Sysdate, ' year ') of the beginning of the date from dual; 
 
 

Next_day (D1[,C1])"function": returns date D1 on the following day (parameter C1) Date "parameter": D1 date Type, C1 for character type (parameter) or numeric type , c1 default is J (i.e. current date) "Parameter table" : C1 Correspondence:
                 Character type: Monday, Tuesday, Wednesday ... Sunday
                        (Win system does not support English week writing) Monday,tuesday,wednesday,thursday,friday,saturday,sunday
                 Digital type: 1,2,3,4,5,6,7
                       Numbers correspond to the week

"Return": Date "Example" If today 2015-04-15 Wednesday, then the following will return the Select Sysdate then date, Next_day (sysdate, ' Monday ')          return: Next week Monday, 2015-04-20next_day ( Sysdate, ' Tuesday ')          
Next_day (sysdate, ' Wednesday ')      
Next_day (sysdate, ' Thursday ')       
Next_day (sysdate,6)                
Next_day (sysdate,7)                returned: Saturday, 2015-04-18
Next_day (sysdate,1)                
from dual;

Extract (c1 from D1)

Function: Date/time D1, parameter (c1) value "parameter": D1 date Type (date)/datetime type (timestamp), C1 as character (parameter) "Parameter table": C1 corresponding parameter table See example "return": Character "Example"

　　

Return:

HOUR MINUTE SECOND Day MONTH
---------- ----------  ---------- ---------- ---------- ----------
19 38 40 12 4 2015

　　

localtimestamp"function": Returns the date and time "parameter" in the session: No arguments, no parentheses "return": Date "Example" select Localtimestamp from dual;

Select Localtimestamp from dual;
Select To_char (Localtimestamp, ' yyyy-mm-dd hh24:mi:ss am ') from dual;

　　

Back to: 12-apr-15 08.17.52.845555 PM
     2015-04-12 20:19:14 PM

current_timestamp"function": Returns the current date "parameter" in the current session timezone with the timestamp with time zone data type: No arguments, no parentheses "return": Date "Example" select Current_ timestamp from dual; return: 12-apr-15 08.45.29.817046 PM +08:00

current_date"function": Returns the current date "parameter" in the current session time zone: No arguments, no parentheses "return": Date "Example" select Current_date from dual; return: 2015-04-12 20:46:23 Sun

dbtimezone"function": Returns the time zone "parameter": no parameters, no parentheses "return": Character type "Example" select Dbtimezone from dual;
     return: +08:00

sessiontimezone"function": Returns the session time zone "parameter": no arguments, no parentheses "return": Character type "Example" select Dbtimezone,sessiontimezone from dual; return: + 00:00   +08:00

INTERVAL C1 Set1"function": variable datetime value "parameter": C1 is a numeric string or datetime string, Set1 is a date parameter "parameter table": Set1 Specific Reference example "return": Numeric value of datetime format, number of preceding + Number is borrowed in days or days in smaller units, such as 1 for 1 days, 1/24 for 1 hours, and 1/24/60 for 1 minutes "example" Selecttrunc (sysdate) + (interval ' 1 ' second),--plus 1 seconds (1/24/60/60 ) trunc (sysdate) + (interval ' 1 ' minute),--plus 1 minutes (1/24/60) trunc (sysdate) + (interval ' 1 ' hour),--Add 1 hours (1/24) trunc (sysdate) + (INTERVAL ' 1 ' Day),  --plus 1 days (1) trunc (sysdate) + (INTERVAL ' 1 ' MONTH),--plus January trunc (sysdate) + (INTERVAL ' 1 ' year),-- Plus 1 years trunc (sysdate) + (interval ' 01:02:03 ' hour to second),--plus specify hours to seconds trunc (sysdate) + (interval ' 01:02 ' minute to second),-- Plus specify minutes to seconds trunc (sysdate) + (interval ' 01:02 ' hour to minute),--plus specify hours to minutes trunc (sysdate) + (interval ' 2 01:02 ' day to minute)--add finger Fixed days to minutes from dual;

How to use Oracle Date-based functions