How to query a specified time range in oracle

In Oracle, you can use "between and" to query data in a specified time range. The syntax is "select*from table name where field between to_date (start time, start time format) and to_date (end time, end time format)".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to query a specified time range in Oracle

In Oracle, if you want to query a specified time range, you can use the between and and to_date() functions.

to_date() and 24-hour notation and mm minute display:

1. When using Oracle’s to_date function to do date conversion, many Java programmers may directly use The format of "yyyy-MM-dd HH:mm:ss" is converted as the format, but it will cause an error in Oracle: "ORA 01810 format code appears twice".

select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual;

The reason is that SQL is not case-sensitive, MM and mm are considered to be the same format codes, so Oracle's SQL uses mi instead of minutes.

select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;

2. If you want to display it in 24-hour format, use HH24

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;// mi is the minute

select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm will display the month The meaning of the to_date parameter in oracle

between and It is a fully closed interval, including the endpoint values.

Note: When using to_date for time filtering, for example, 2015-02-28 is calculated from 00:00:00 on the day. Therefore, if the range is from 2015-02-28 to 2015-03-01, the time range is actually closed on the left and open on the right. Data on the day 2015-03-01 is not included.

Examples are as follows:

select * from tab where dtcol between to_date('2012-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-05-31 00:00:00','yyyy-mm-dd hh24:mi:ss')

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to query a specified time range in oracle. For more information, please follow other related articles on the PHP Chinese website!