Date Functions in Oracle

Sysdate

Feature: Returns the current date.

"Parameters": no arguments, no parentheses

"Back": Date

• 2

  Add_months (D1,N1)

  "Function": Returns the new date after the date D1, plus N1 months.

  "Parameters": D1, Date type, N1 digital type

  "Back": Date

• 3

  Last_day (D1)

  Features: Returns the date D1 the last day of the month.

  "Parameters": D1, Date type

  "Back": Date

• 4

  Months_between (D1,D2)

  Function: Returns the number of months between the date D1 to the date D2.

  "Parameters": d1,d2 Date Type

  "Return": number if D1>D2, returns positive if D1<D2, negative

  "Example" Select Sysdate, Months_between (sysdate,to_date (' 2006-01-01 ', ' yyyy-mm-dd ')), Months_between (Sysdate,to_date (' 2016-01-01 ', ' Yyyy-mm-dd ')) from dual;

• 5

  New_time (DT1,C1,C2)  

  Function: Gives time dt1 the date and time in the C1 time zone corresponding to the C2 time zone  

  "parameter": dt1,d2 date type  

    "return": DateTime  

    parameters: C1,c2 corresponding time zone and shorthand    

  Atlantic Standard Time: AST or ADT   &NBSP;

  Alaska _ Hawaii Time: HST or HDT    

  UK Daylight Time: BST or BDT    

  US Mountain Time: MST or MDT   &NBSP;

  US Central Time zone: CST or CDT   

     Newland Standard Time: NST    

  Eastern US time: EST or EDT    

  Pacific Standard Time: PST or PDT   &NBSP;

  Greenwich Mean Time: GMT    

  Yukou Standard Time: Yst or ydt 

• 6

  Round (D1[,C1])

  "Features": given date D1 The first day of the period after rounding (parameter C1) (similar to the value rounding meaning)

  "Parameters": D1 Date Type, C1 as character (parameter), C1 default is J (i.e. last 0 points date)

  "Parameter table": C1 corresponding parameter table: Last 0 Point date: Cancel parameter C1 or J

  Last Sunday: day or dy or D

  Last month Date: Month or Mon or mm or RM

  Latest season Date: Q

  Latest beginning Date: Syear or year or yyyy or yyy or yy or y (multiple y means precision)

  Recent century at the beginning date: CC or SCC

  "Back": Date

• 7

  Trunc (D1[,C1])

  Function: Returns the date D1 the first day of the period (parameter C1)

  "Parameters": D1 Date Type, C1 as character (parameter), C1 default is J (i.e. current date)

  "Parameter table": C1 corresponding parameter table:

  Last 0 Point date: Cancel parameter C1 or J

  Most recent Sunday: day or Dy or D (Weekly order: Days, one, two, three, four, five, six)

  Last month Date: Month or Mon or mm or RM

  Latest season Date: Q Latest beginning Date: Syear or year or yyyy or yyy or yy or y (multiple y means precision)

  Recent century at the beginning date: CC or SCC

  "Back": Date

• 8

  Next_day (D1[,C1])

  "Features": return date D1 in next week, Day of the week (parameter C1)

  "Parameters": D1 Date Type, C1 as character (parameter), C1 default is J (i.e. current date)

  "Parameter table": C1: Monday, Tuesday, Wednesday ... Sunday

  "Back": Date

  Extract (c1 from D1)

• Function: The value of the parameter (c1) in the date/time D1

  "Parameters": D1 Date Type (date)/datetime (timestamp), C1 as character (parameter)

  "Parameter table": C1 corresponding parameter table See example

  "Return": Character

  "Example" Select Extract (Hour from timestamp ' 2001-2-16 2:38:40 ') hours, extract (minute from timestamp ' 2001-2-16 2:38:40 ') minutes , extract (second from timestamp ' 2001-2-16 2:38:40 ') seconds, extract (Day from timestamp ' 2001-2-16 2:38:40 '), Extract (MON TH from timestamp ' 2001-2-16 2:38:40 ') month, extract (year from timestamp ' 2001-2-16 2:38:40 ') years from dual;

  Select Extract (Year from date ' 2001-2-16 ') from dual;

• 10

  Localtimestamp

  Features: Returns the date and time in a session

  "Parameters": no arguments, no parentheses

  "Back": Date

• 11

  Current_timestamp

  "Features": Returns the current date in the current session time zone with the timestamp with period data type

  "Parameters": no arguments, no parentheses

  "Back": Date

• 12

  Current_date

  Features: Returns the current date in the current session's time zone

  "Parameters": no arguments, no parentheses

  "Back": Date

  "Example" select Current_date from dual;

  13

• Dbtimezone

  "Features": Return time zone

  "Parameters": no arguments, no parentheses

  "Back": Character type

• 14

  Sessiontimezone

  Features: Return to the session time zone

  "Parameters": no arguments, no parentheses

  "Back": Character type

  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": a numeric value that precedes multiple + numbers in days or days in smaller units, such as 1 for 1 days, 1/24 for 1 hours, 1/24/ 60 means 1 minutes  

    Sample Select Trunc (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)--plus a specified number of days to minutes

   from dual;

  end

Date functions in Oracle