Oracle-date-based Data Query tips, oracle-date Query

Oracle-date-based Data Query tips, oracle-date Query

The date type is a common type for storing date classes in oracle, and its processing is also worth more attention in database usage. For example, you can use the to_char function to convert the string to any time format, or use the to_date function to convert the corresponding string to the date format.

This article mainly introduces such a usage.

Problem description: The field is saved as a date in the format of hour, minute, and second. You need to query data in a certain day or a certain date range. That is, the query condition has no time, minute, and second.

Problem Analysis: Because the storage format comes with time and minute, when between and is used to query data in a certain day or in a date range, the data between the start time and the end time is actually queried, as a result, the query data is lost.

Solution:

1. Many articles provide this method, that is, splicing. Shape

       select *         from *        where $$$ between              to_date('16/01/2018 00:00:00', 'dd/MM/yyyy hh24:mi:ss') and              to_date('16/01/2018 23:59:59', 'dd/MM/yyyy hh24:mi:ss')

Where

16/01/2018 00:00:00，

16/01/2018 23:59:59

This method is easy to understand.

2. Use oracle's to_date and to_char in combination. Shape

select *  from * where (to_date(to_char($$$,'dd/MM/yyyy'),'dd/MM/yyyy') Between       to_date('16/01/2018', 'dd/MM/yyyy') AND       to_date('16/01/2018', 'dd/MM/yyyy'))

This method is very convenient.