How to query date in oracle (a brief analysis of various methods)

Oracle is a powerful database management system that provides many powerful SQL query statements to meet our different needs. Among them, date query is one of the operations we often need to use.

In Oracle, date query can be performed in various ways. Here we will introduce some common query methods.

1. Query data on a specified date

If we need to query data on a certain day, we can use the following SQL statement:

SELECT *
FROM sales
WHERE sales_date = TO_DATE('2020-01-01','YYYY-MM-DD');

Among them, '2020- 01-01' represents the date to be queried, and the TO_DATE function converts it to date type.

2. Query data within a certain time period

If we need to query data within a certain time period, we can use the following SQL statement:

SELECT *
FROM sales
WHERE sales_date BETWEEN TO_DATE('2020-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') 
    AND TO_DATE('2020-01-31 23:59:59','YYYY-MM-DD HH24:MI:SS');

Among them, '2020-01-01 00:00:00' and '2020-01-31 23:59:59' represent the start and end time of the time period respectively, and the BETWEEN and AND keywords specify the time range of the query .

3. Query last month’s data

If we need to query last month’s data, we can use the following SQL statement:

SELECT *
FROM sales
WHERE sales_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
    AND sales_date < TRUNC(SYSDATE, &#39;MM&#39;);

Among them, ADD_MONTHS The function is used to calculate the date of the previous month, and the TRUNC function is used to adjust the current date to the first day of the month, which ensures that we are querying the data of the previous month.

4. Query the data of the last week

If we need to query the data of the last week, we can use the following SQL statement:

SELECT *
FROM sales
WHERE sales_date >= TRUNC(SYSDATE) - 7
    AND sales_date < TRUNC(SYSDATE);

Among them, the TRUNC function uses By adjusting the current date to zero o'clock on that day, this ensures that we are querying data for the past week.

5. Query the data at the current time

If we need to query the data at the current time, we can use the following SQL statement:

SELECT *
FROM sales
WHERE sales_date >= TRUNC(SYSDATE)
    AND sales_date < TRUNC(SYSDATE) + 1;

Among them, the TRUNC function uses The current date is adjusted to zero o'clock on that day, and adding 1 is to query the data before zero o'clock the next day.

Summary:

The above are some common methods of date query operations in Oracle. Through these methods, we can easily query the data we need. For business systems that require frequent date queries, these methods also provide some useful ideas and techniques.

The above is the detailed content of How to query date in oracle (a brief analysis of various methods). For more information, please follow other related articles on the PHP Chinese website!