CODEWITHSUNDEEP
oracle-databaseto-date
greenbay007845
greenbay007845

Reputation: 77

Oracle: Why do I have to use TO_DATE to pull my data?

When trying to filter on EXPIRE_DATE, it seems I have to use TO_DATE. Why do I have to use TO_DATE? The EXPIRE_DATE data type in the database is ALREADY set to date. Here is the code that works.

SELECT * FROM MY_TABLE
  WHERE EXPIRE_DATE >= TO_DATE('2020/01/13','yyyy/mm/dd')
  AND EXPIRE_DATE <= TO_DATE('2020/04/19','yyyy/mm/dd')

I tried to use BETWEEN without TO_DATE and just use my dates but I received an error.

To recap, even though the data type for this is ALREADY date, it seems I have to use TO_DATE to pull my data when I want to filter. Is there something I am missing? Here is my error when I try filter my data without using TO_DATE.

my error

Upvotes: 3

Views: 1306

Answers (3)

Popeye
Popeye

Reputation: 35910

Apart from some good answers here, I would like to tell you that you do not need TO_DATE to pull the data from your table.

You need to_date or date literal to convert the normal string to date which can be compared to the column data in your table, As the date column must be compared with the date data type variable/constant.

To convert normal string to date, You can use the following:

TO_DATE('2020/01/13','yyyy/mm/dd')
DATE '2020-01-13'

I would not recommend using NLS_DATE_FORMAT just for creating the date.

Upvotes: 3

alvalongo
alvalongo

Reputation: 571

You can use BETWEEN:

Warning: it's better to use always TO_DATE function with proper format string and avoid implicit conversions that in some occasions produces strange behaviors in the results.

Warning: all columns of type DATE have always the hour-minute-second component. if you forget this you may have fewer records in the result.

Example:

create table my_table
(id          number,
 expire_date date
);

Some data:

insert into my_table values ( 4,to_date('2011-06-17 10:07:18','yyyy-mm-dd hh24:mi:ss')); 
insert into my_table values (12,to_date('2010-10-01 17:43:30','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (13,to_date('2011-07-30 08:38:34','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (21,to_date('2010-04-22 07:03:35','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (26,to_date('2011-03-26 02:07:57','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (35,to_date('2010-09-16 17:40:01','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (38,to_date('2011-11-05 17:27:45','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (44,to_date('2011-12-25 04:51:24','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (45,to_date('2011-11-05 03:08:51','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (54,to_date('2011-09-22 18:29:14','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (78,to_date('2010-03-12 20:23:21','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (79,to_date('2011-05-19 17:30:15','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (83,to_date('2011-11-15 10:04:58','yyyy-mm-dd hh24:mi:ss'));
insert into my_table values (96,to_date('2011-03-11 20:14:30','yyyy-mm-dd hh24:mi:ss'));

Set default date format to ISO-8601 international format: 

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Query using implicit conversion:

SELECT a.*
fROM my_table a
where expire_date between '2010-01-01 00:00:00'
                      and '2010-12-31 23:59:59';

Answer:

ID EXPIRE_DATE        
12 2010-10-01 17:43:30
21 2010-04-22 07:03:35
35 2010-09-16 17:40:01
78 2010-03-12 20:23:21

but using US date format month-day-year:

SELECT a.*
fROM my_table a
where expire_date between '01/01/2010 00:00:00'
                      and '12/31/2010 23:59:59';

You got error:

ORA-01861: literal does not match format string

Change session date format to US format:

alter session set nls_date_format='MM-DD-YYYY HH24:MI:SS';

you can write dates in US format:

SELECT a.*
fROM my_table a
where expire_date between '01/01/2010 00:00:00'
                      and '12/31/2010 23:59:59';

And the answer is:

        ID EXPIRE_DATE
---------- -------------------
        12 10-01-2010 17:43:30
        21 04-22-2010 07:03:35
        35 09-16-2010 17:40:01
        78 03-12-2010 20:23:21

Upvotes: 1

MT0
MT0

Reputation: 167981

You don't need to use TO_DATE, instead you can use a DATE literal:

SELECT *
FROM   MY_TABLE
WHERE  EXPIRE_DATE >= DATE '2020/01/13'
AND    EXPIRE_DATE <= DATE '2020/04/19'

Or, if your NLS_DATE_FORMAT session parameter matches YYYY/MM/DD then you can insert the values as strings and rely on implicit string conversion (don't do this though):

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';

SELECT *
FROM   MY_TABLE
WHERE  EXPIRE_DATE >= '2020/01/13'
AND    EXPIRE_DATE <= '2020/04/19'

But it is not good practice to rely on the NLS_DATE_FORMAT as ANY user can change their own value at ANY time so your query can randomly fail when users start changing these values.

Upvotes: 1

Related Questions