16 oracle query date statements

16 oracle query date statements are frequently used in the database query process. The following describes 16 oracle query date statements, each oracle query date statement provides a function, which is helpful to you. Www.2cto.com week 1: Obtain the week of the current month SQL> select to_char (sysdate, 'yyyymmdd W HH24: MI: ss') from dual; TO_CHAR (SYSDATE, 'yy ------------------- 20030327 4 18:16:09 SQL> select to_char (sysdate, 'w') from dual; T-4 www.2cto.com today 2: Get the current date is the day of the week, note that Sunday is the first day of SQL> select sysdate, to_char (sysdate, 'D') from dual; SYSDATE T ----------27-MAR-03 5 is similar to: select to_char (sysdate, 'yyyy') from dual; -- select to_char (sy Sdate, 'q' from dual; -- quarter select to_char (sysdate, 'mm') from dual; -- month select to_char (sysdate, 'dd') from dual; -- the DAY in the year of ddd. The DAY in the year of WW. W. The DAY in the week in the DAY of the month. D. The NUMBER displayed today is '1', 'sunday ', '2', 'monday', '3', 'tuesday', '4', 'wedday', '5', 'thurs', '6', 'Friday ', '7', 'satur' hh hour (12) hh24 hour (24) Mi minute ss second hour 3: Take the current date as the day of the week Chinese display: www.2cto.com SQL> select to_char (sysdate, 'day') from dual; TO_CHAR (SYSDATE, 'day ')--------------------- -Thursday limit 4: If a table is indexed on a date field, how to use alter session set NLS_DATE_FORMAT = 'yyyy-MM-DD HH24: MI: ss' limit 5: obtain the current date select sysdate from dual; values 6: Get the date select trunc (sysdate) from dual at 00:00:00 on the current day; -- get the last second of the day select trunc (sysdate) + 0.99999 from dual; -- Obtain the specific hourly value select trunc (sysdate) + 1/24 from dual; select trunc (sysdate) + 7/24 from dual; limit 7: obtain the date from dual; select trunc (sysdate + 1) at 00:00:00 tomorrow morning (Sysdate) + 1 from dual; week 8: select trunc (sysdate, 'mm') from dual; week 9: Get the date select trunc (add_months (sysdate, 1), 'mm') from dual; limit 10: return the last day of the current month? Select last_day (sysdate) from dual; select last_day (trunc (sysdate) from dual; select trunc (last_day (sysdate) from dual; select trunc (add_months (sysdate, 1 ), 'mm')-1 from dual; period 11: Obtain the select trunc (sysdate, 'yyyy') every day of the year ') + rn-1 date0from (select rownum rn from all_objectswhere rownum <366); Limit 12: Today is the nth day of this year SELECT TO_CHAR (SYSDATE, 'ddd ') from dual; limit 13: how to add 2-year select add_months (sysdate, 24) from dual; period to an existing date 14: Determine whether the year of a day is run by select decode (to_char (last_day (trunc (sysdate, 'y') + 31), 'dd'), '29 ', 'leap year', 'Year') from dual; Limit 15: judge whether two years later is the runyear select decode (to_char (last_day (trunc (add_months (sysdate, 24), 'y ') + 31), 'dd'), '29', 'leap year', 'flat year') from dual; Limit 16: obtain the quarter www.2cto.com select ceil (to_number (to_char (sysdate, 'mm')/3) from dual; select to_char (sysdate, 'q') from dual; the preceding section describes the oracle query date statement. ORACLE fuzzy search practice on the day select count (*) from t3 where update_time like to_char (sysdate, 'yyyy-mm-dd') | '%'; mysql select ca. ID, ca. STATUS, cain. CONTENT_CODE from T_DM_CASE ca, T_DM_CASEINFO cain where ca. TYPE <> '6' and ca. CREATE_TIME like concat (curdate (), '% ')