Oracle date calculation methods

The first day of a month

 

 

SELECT to_date(to_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd')FROM dual

Sysdate is the current system time of the database server.
To_char is a function that converts a date type into a date type.
To_date is a function that converts the string type to the date type. Generally, the format is yyyy-mm-dd hh24: mi: ss. If no time is specified, the default time is 00:00:00.

The dual table is a table of sys users. This table has only one record and can be used to calculate some expressions. If a user logs on to the system using sys and adds a record to the dual table, then the system 99.999% cannot be used. Why not use the sys. dual format? Because sys has created aliases available to all users for the dual table.

The first day of this month

select to_char(trunc(add_months(last_day(sysdate), -1) + 1), 'yyyy-mm-dd') 

Last day of this month

 

select to_char(last_day(sysdate), 'yyyy-mm-dd')

The first day of a year

 

 

SELECT to_date( to_char(SYSDATE,'yyyy')||'-01-01','yyyy-mm-dd') FROM dual

 

First day of the quarter

SELECT to_date(               to_char(SYSDATE,'yyyy-')||               lpad(floor(to_number(to_char(SYSDATE,'mm'))/3)*3+1,2,'0')||               '-01',               'yyyy-mm-dd')FROM dual

Floor is rounded down
Lpad is used to expand the string to the left using the specified character. This extended string is up to two bits, with less than '0' supplemented '.

Midnight of that day
SELECT trunc (SYSDATE) + 1-1/24/60/60
FROM dual

Trunc truncates the time part of sysdate, that is, the time part is changed to 00:00:00
In Oracle, the date addition and subtraction is performed based on the number of days. Therefore, + 1-1/24/60/60 changes the time part to 23:59:59.
Oracle 8i only supports time to seconds, and 9i or above supports 1/100000000 seconds.

Last day of last month

 

SELECT trunc(last_day(add_months(SYSDATE,-1)))+1-1/24/60/60FROM dual

Add_months is the addition and subtraction function of the month.
Last_day is the function used to calculate the last day of the month.

Last day of this year

SELECT trunc(     last_day(to_date(to_char(SYSDATE,'yyyy')||'-12-01','yyyy-mm-dd'))    )+1-1/24/60/60FROM dual

The first Monday of this month

SELECT next_day (to_date (to_char (SYSDATE, 'yyyy-mm') | '-01', 'yyyy-mm-dd'), 'monday ') FROM dualnext_day is the first compliant date FROM the specified date. The 'monday' here is slightly different according to the setting of NLS_DATE_LANGUAGE.

 

Remove hour/minute/second

select trunc(sysdate) from dual

Obtain the number of days in a month

SELECT trunc(last_day(SYSDATE))-       to_date(to_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd')+       1FROM dual

Determines whether a leap year is used.

SELECT decode (to_char (last_day (to_date (to_char (SYSDATE, 'yyyy') | '-02-01', 'yyyy-mm-dd'), 'dd '), '28', 'Year', 'Year') FROM dual

How many days a quarter

SELECT last_day (to_date (to_char (SYSDATE, 'yyyy-') | lpad (floor (to_number (to_char (SYSDATE, 'mm')/3) * 3 + 3, 2, '0') | '-01', 'yyyy-mm-dd')-to_date (to_char (SYSDATE, 'yyyy -') | lpad (floor (to_number (to_char (SYSDATE, 'mm')/3) * 3 + 1, 2, '0') | '-01 ', 'yyyy-mm-dd') + 1 FROM dual this article is FROM the CSDN blog. For more information, see file: // E: /knowledge accumulation/oracle/Program Development % 20Oracle various date calculation methods (favorites) % 20-% 20fish2008 column % 20-% 20CSDN blog. mht