Obtain a specific date and time (need to H) in Oracle)

Select trunc (SYSDATE-1, 'Year')-1, 'Year') from dual -- Get first day of last YEAR, sysdate-1 indicate current day in DW case.

Select add_months (trunc (sysdate, 'Year'), 12)-(1/86400) from dual -- get the last minute and second of the year.

Select trunc (TO_DATE ('20140901', 'yyyymmdd'), 'month') AS FIRST_DAY_MONTH from dual --- obtain the first day of the current MONTH

Select trunc (SYSDATE, 'month') AS FIRST_DAY_CURRENT_MONTH from dual; -- the first day of the current MONTH is obtained.

 

--- Obtain the last time point from the first day of last month of last year to the last day of last month of this year. If the current value is 2011-09, to 23:59:59 is returned.

Select trunc (ADD_MONTHS (SYSDATE,-13), 'month '),-- Fist_day_of_previus_month_of_last_year

TRUNC (SYSDATE, 'month')-(1/86400)-- End_datetime_of_last_month

From dual;

 

Select trunc (SYSDATE, 'month')-(1/86400) from dual; -- Obtain the last time of the last day of the last MONTH.

 

--- Obtain the start and end dates of the last month of the specified month.

Select trunc (ADD_MONTHS (SYSDATE,-2), 'month') AS fisrt_day_of_last_last_month,

Trunc (ADD_MONTHS (SYSDATE,-1), 'salesman')-(1/86400) AS Last_Day_of_last_Last_Month

From dual;

Select trunc (ADD_MONTHS (to_date ('201312', 'yyyymmdd'),-2), 'month') AS fisrt_day_of_last_last_month,

Trunc (ADD_MONTHS (to_date ('20140901', 'yyyymmdd'),-1), 'month')-(20110220) AS Last_Day_of_last_Last_Month

From dual;

--- Obtain the two methods at the last time of the last day

Select (TRUNC (NEXT_DAY (SYSDATE, 1), 'day')-(1/86400)-3 from dual;

SELECT TO_DATE (TO_CHAR (SYSDATE, 'yyyy-MM-DD '), 'yyyy-MM-DD')-(1/86400) from dual;