Oracle Date-time functions

Source: Internet
Author: User
Tags central time zone date1 month name truncated

---------------------------------------------Date/Time function-------------------------------------------------1:   Sysdate is used to get the current date of the system select Sysdate from Dual;--2:  add_months Add or Subtract month select To_char (add_months (' 20080818 ' , ' YYYYMMDD '), 2), ' Yyyy-mm-dd ') from DUAL; Select To_char (Add_months (Sysdate,-1), ' Yyyy-mm-dd ') from dual--3:   last_day return date last day select Last_day (sysdate) From DUAL; SELECT Last_day (Add_months (Sysdate,-2)) from dual;--4:   Months_between (DATE2,DATE1) Given the date2-date1 month-the format of the parameter requires attention to select Months_between (to_date (' 2011-05-03 ', ' yyyy-mm-dd '), to_date (' 2011-01-23 ', ' Yyyy-mm-dd ')) from DUAL; SELECT Months_between (' 1 September-December-1999 ', ' 1 September-March -1999 ') Mon_between from Dual;--select months_between (' 20 November-January-23 ', ' 20 November-September-1 ') from DUAL;   Text does not match the format string--5:    new_time (date, ' This ', ' that ') gives the date and time in the This time zone =other time zone select To_char (sysdate, ' YYYY. Mm. DD HH24:MI:SS ') beijing_time,       to_char (New_time (sysdate, ' PDT ', ' GMT '), ' YYYY. Mm. DD HH24:MI:SS ') Los_angelS from dual;                abbreviated             &NBSP ;     Time zone                  ast OR ADT           Atlantic Standard Time       &NBSP;&NBSP;&NBSP;HST or HDT           Alaska-Hawaii time  bst or B DT           UK daylight saving time  mst or MDT           US mountainous times  cst or CDT           US Central Time zone  nst                 Newland standard  est OR ED T           US Eastern time  pst OR PDT           Pacific Standard Time &NBSP;GMT   &NBSP ;             Grenwy Standard Time  yst OR YDT          yukon Standard Time    --6:    NEXT_DAY   returns the exact date of the Sunday number that you requested in the same one-week or one-week period after the date of enactment.    --Sunday = 1   Monday = 2   Tuesday = 3   Wednesday= 4   Thursday = 5   Friday = 6   Saturday = 7   select next_day (Sysdate, 2) from dual;  --7:   &N Bsp current_date  current date in the current session time zone  select current_date from dual;--8:    current_timestamp to timestamp with The time zone data type returns the current date in the current session timezone select Current_timestamp from dual;--9:   Dbtimezone ()   Return time zone select Dbtimezone from DUAL;--10:   Sessiontimezone return to the time zone   where Dbtimezone is the database, the session is for the current session, because the time zone can change select Sessiontimezone at the session level From DUAL;  alter SESSION SET time_zone = ' 8:00 '; SELECT Sessiontimezone from DUAL;  --11:EXTRACT  Find the field value for the date or interval value  select EXTRACT (month from sysdate) "Month" from DUAL; Select EXTRACT (Day  from sysdate)  as ' Day ' from DUAL; Select EXTRACT (year from sysdate) as ' year ' from dual;--12:localtimestamp  returns the date and time in the session  select Localtimestamp  From dual;--13: The  trunc (for dates) TRUNC function truncates the date value for the specified element. Its specific syntax format is as follows: TRUNC (Date[,fmt]) Where: Date a datetime value FMT date format, the date will be truncated by the specified element format. Ignoring it is truncated by the nearest date. The following is the function that causes theUsage: TRUNC (to_date (' 24-nov-1999 "), ' dd-mon-yyyy hh:mi am ') = ' 24-nov-1999 12:00:00 am ' TRUNC (to_date (' 24-nov-199 9 08:37 pm ', ' dd-mon-yyyy hh:mi am '), ' hh ') = ' 24-nov-1999 08:00:00 am '  select TRUNC (sysdate, ' yyyy ') from DUAL;  -Return to the first day of the year.  select TRUNC (sysdate, ' MM ') from DUAL;  -Returns the first day of the month. SELECT TRUNC (sysdate, ' D ') from DUAL;  --Returns the first day of the current week. Select TRUNC (sysdate, ' DD ') from dual;--returns the current month day----  Last day of last month select To_char (Last_day (Add_months,-1)), ' Yyyy/mm/dd ') from DUAL;----: Today of the previous month select To_char (add_months (Sysdate,-1), ' Yyyy-mm-dd ') from DUAL;----The first day of last month select To_ CHAR (Add_months (Last_day (sysdate) +1,-2), ' Yyyy-mm-dd ') firstday from DUAL;---  by weekly statistics select TO_CHAR (sysdate, ' WW ') from DUAL;---  According to monthly statistics select TO_CHAR (sysdate, ' MM ') from DUAL;----  statistics per quarter select To_char (sysdate, ' Q ') from DUAL;---  according to annual statistics SELECT to_char (sysdate, ' YYYY ') from DUAL;---  to find specific dates for all Friday in a month  select To_char (T . D, ' Yy-mm-dd ') from (    SELECT TRUNC (sysdatE, ' mm ') + ROWNUM-1 as d    from dba_objects    WHERE ROWNUM < +) Twhere To_char (T.D, ' mm ') = To_ CHAR (sysdate, ' MM ')     and TRIM (To_char (T.D, ' day ') = ' Friday ' below should belong to the Format function, but in order to do a detailed description of the date function, the To_ The contents of CHAR are placed here Y or yy or YYY last, two-bit or three-bit  select to_char (sysdate, ' YYY ') from DUAL; SELECT to_char (sysdate, ' YY ') from DUAL; Syear or year syear make BC a minus sign select To_char (sysdate, ' syear ') from DUAL;    --twenty Elevenq Quarter, January-March for the first quarter  select to_char (sysdate, ' Q ') from DUAL;        --2 indicates the second quarter MM months  select  to_char (sysdate, ' mm ') from DUAL;     &NBSP;--04 said that the April  rm month of Rome indicated  select To_char (sysdate, ' RM ') from DUAL;      --IV represents the month name  select to_char (sysdate, ' month ') from DUAL April  month with a 9-character length;    --April &NBSP;WW The first few weeks  select to_char (sysdate, ' WW ') from DUAL;      -24 means June 13, 2002 is the 24th week  w  select to_char (sysdate, ' W ') from DUAL in the first weeks of the month;        --April 26, 2011 is the day of the 4th week &NBSP;DDD year. January 1 is 001, February 1 for 032 select to_char (sysdate, ' DDD ') from Dual;dd the day of the month  select To_char (sysdate, ' DD ') from Dual;d The day of the week  select To_char (sysdate, ' D ') from Dual;dy weeks within the first day abbreviation  select to_char (sysdate, ' DY ') from DUAL; HH or HH12 12 hours of input select To_char (sysdate, ' HH ') from DUAL; HH24 24-hour  select to_char (sysdate, ' HH24 ') from Dual;mi minutes (0~59)  select to_char (sysdate, ' MI ') from DUAL; Note Do not use mm format for minutes (MI should be used in minutes). MM is the format used for the month, and it works for minutes, but the result is wrong. SS seconds (0~59)  select to_char (sysdate, ' SS ') from dual; ---------------------------------------------------- ----------------------------------

Oracle Date-time function

Related Article

Contact Us

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.