How to compare different format date in oracle?

Question

i have a table that use date, and need to return row between certain date.

column name is tg_konfi and column format is DATE, and tg_konfi date is 02-FEB-21 like that.

i have try this

SELECT * From table where to_date(tg_konfi,'DD-MON-YY') between to_date('2021-01-01','YYYY-MM-DD') and to_date('2021-10-07','YYYY-MM-DD');

and its return nothing. Am i missing something, or I do this wrong?

Is tg_konfi defined as a DATE column? If so you don’t need to use to_date. — Del, Oct 07 '21 at 03:01
There is no need to call `TO_DATE` on a value which is already a `DATE`. `02-FEB-21` is just the **default** display format according to `NLS_DATE_FORMAT` session parameter. — Wernfried Domscheit, Oct 07 '21 at 07:04

score 2 · Accepted Answer · answered Oct 07 '21 at 04:06

2

As tg_confi is a date datatype column, do not use the to_date on that column.

Use the following query:

SELECT * 
  From table 
 where tg_konfi between to_date('2021-01-01','YYYY-MM-DD') 
                    and to_date('2021-10-07','YYYY-MM-DD');

answered Oct 07 '21 at 04:06

Popeye

35,427
4
10
31

score 0 · Answer 2 · answered Oct 07 '21 at 07:49

A date does not have a format - it is stored internally to the database as 7-bytes (representing century, year-of-century, month, day, hour, minute and second) and it is not until whatever user interface you are using (i.e. SQL/Plus, SQL Developer, Java, etc.) tries to display it to you, the user, that is is converted it into something you would find meaningful (usually a string) that the date has a format (and that conversion is done by the user interface and not by the database).

If your values are all DATE data types then this means that you do not need to worry about any format - because they won't have a format in the database.

The simplest method is to use date literals:

SELECT *
FROM   table
WHERE  tg_konfi BETWEEN DATE '2021-01-01'
                    AND DATE '2021-10-07';

or explicitly convert from a string literal using TO_DATE:

SELECT *
FROM   table
WHERE  tg_konfi BETWEEN TO_DATE( '2021-01-01', 'YYYY-MM-DD' )
                    AND TO_DATE( '2021-10-07', 'YYYY-MM-DD' );

This will get all rows where tg_konfi is between 2021-01-01T00:00:00 and 2021-10-07T00:00:00.

If you want the range to include up to 2021-10-07T23:59:59 then you can use:

SELECT *
FROM   table
WHERE  tg_konfi >= DATE '2021-01-01'
AND    tg_konfi <  DATE '2021-10-07' + INTERVAL '1' DAY; -- or DATE '2021-10-08'

How to compare different format date in oracle?

2 Answers2