[Reprinted] oracle-related time calculation, with the first and last day of the quarter

Blog. csdn. netjjgjlfarticledetails6640346 small Oracle date week first day of this quarter SELECTto_char (TRUNC (SYSDATE, Q), YYYY-MM-DD) FROMdual; -- previous

Http://blog.csdn.net/jjgjlf/article/details/6640346 dig Oracle date ---------------------------------------- -- SELECT to_char (TRUNC (SYSDATE, 'q'), 'yyyy-MM-DD ') FROM dual; -- previous

Http://blog.csdn.net/jjgjlf/article/details/6640346

------------------------------------------------ Oracle date ------------------------------------------

-- The first day of this quarter
SELECT to_char (TRUNC (SYSDATE, 'q'), 'yyyy-MM-DD ') FROM dual;


-- Last day of the last quarter (you can use the first day of the current quarter minus 1)
SELECT to_char (TRUNC (SYSDATE, 'q')-1, 'yyyy-MM-DD ') FROM dual;


-- The first day of the previous quarter (the first day of this quarter minus three months)
SELECT to_char (add_months (TRUNC (SYSDATE, 'q'),-3), 'yyyy-MM-DD ')
FROM dual;


-- First day of the previous quarter (the last day of the month after one month in this quarter)
Select to_char (last_day (add_months (trunc (sysdate, 'q'),-1), 'yyyy-mm-dd ')
From dual

-- Week 1: Obtain the week number of the current month.
Select to_char (sysdate, 'yyyymmdd W HH24: MI: ss') from dual;
Select to_char (sysdate, 'w') from dual;


-- Week 2: Get the current date as the day of the week. Note that Sunday is the first day of the week.
Select sysdate, to_char (sysdate, 'D') from dual;
-- Similar:
Select to_char (sysdate, 'yyyy') from dual; -- current year
Select to_char (sysdate, 'q') from dual; -- current quarter
Select to_char (sysdate, 'mm') from dual; -- current month
Select to_char (sysdate, 'dd') from dual; -- current day




-- Week 3: display the Chinese version of the day of the week on the current date:
Select to_char (sysdate, 'day') from dual;


-- Comment 4: If a table is indexed on a date field, how can we use
Alter session set NLS_DATE_FORMAT = 'yyyy-MM-DD HH24: MI: ss'


-- Expected 5: Get the current date
Select sysdate from dual;


-- IPv6: Get the date of 00:00:00 on the current day.
Select to_char (trunc (sysdate), 'yyyymmdd hh: mi: ss') from dual;


-- Get the last second of the day
Select trunc (sysdate) + 0.99999 from dual;


-- Obtain the hour value.
Select trunc (sysdate) + 1/24 from dual;
Select trunc (sysdate) + 7/24 from dual;


-- Release 7: Get the date of 00:00:00 tomorrow morning
Select trunc (sysdate + 1) from dual;
Select trunc (sysdate) + 1 from dual;


-- August 8: the date of January 1, 1st day of this month
Select trunc (sysdate, 'mm') from dual;


-- December 9: Get the date of December 9.
Select add_months (trunc (sysdate, 'mm'), 1) from dual;
Select trunc (add_months (sysdate, 1), 'mm') from dual;


-- Lifecycle 10: returns 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;


-- Release 11: Get the first day of the year
Select trunc (sysdate, 'y') from dual


-- 11th 11: Get every day of the year
Select trunc (sysdate, 'yyyy') + rn-1 date0
From (select rownum rn from all_objects where rownum <366) t;


-- Jun 12: Today is the nth day of this year.
SELECT TO_CHAR (SYSDATE, 'ddd ') from dual;


-- Lifecycle 13: how to add two years to an existing date
Select add_months (sysdate, 24) from dual;


-- 14: determines whether the year of a certain day is a runyear.
Select last_day (trunc (sysdate, 'yyyy') + 31) from dual -- add 31 days to the first day of a year, and then take the last day
Select decode (to_char (last_day (trunc (sysdate, 'y') + 31), 'dd '),
'29 ',
'Leap year ',
'Year ')
From dual;


-- Renewal 15: judge whether the year is a runner-up after two years
Select decode (to_char (last_day (trunc (add_months (sysdate, 24), 'y') + 31), 'dd '),
'29 ',
'Leap year ',
'Year ')
From dual;


-- Quarter 16: Obtain the quarter of the date
Select trunc (sysdate, 'mi') from dual;
Select to_number (to_char (sysdate, 'mm') from dual;
Select ceil (to_number (to_char (sysdate, 'mm')/3) from dual;
Select to_char (sysdate, 'q') from dual;


-- No17: returns the last day of a month.

Select to_char (last_day (sysdate), 'dd') from dual

-- The first and last days of the week, considering that the Chinese are used to Monday as the first day of the week (the International Convention is Sunday as the first day, as does oracle)
Select trunc (sysdate, 'D') + 1, trunc (sysdate, 'D') + 7 from dual where to_char (sysdate, 'day') <> 'sunday'
Union all
Select trunc (sysdate, 'D') + 1-7, trunc (sysdate, 'D') + 7-7 from dual where to_char (sysdate, 'day ') = 'sunday'

-- The first day of the current quarter
Select to_char (trunc (sysdate, 'q'), 'yyyy-mm-dd') from dual;
-- The last day of the current quarter
SELECT to_char (add_months (TRUNC (SYSDATE, 'q'), + 3)-1, 'yyyy-MM-DD ')
FROM dual;

Select to_char (trunc (add_months (last_day (sysdate),-1) + 1), 'yyyy-mm-dd') "first day of the month ",
To_char (last_day (sysdate), 'yyyy-mm-dd') "last day of the month" FROM dual;