SQL - How to count number of data in certain year?

user_14809795 0
Viewed 189

I'm trying to count the number of orders made in the year 2019 however I keep getting an error

ORA-01861: literal does not match format string

The SQL that I tried running is:

SELECT COUNT(*) 
FROM Order_tab
WHERE Order_Date = '%2019';

The table format is

create table Order_tab 
(   
    Order_ID number (11,0) not null,
    Order_Date date DEFAULT SYSDATE,
    Customer_ID number (11,0),

    constraint Order1_PK 
        primary key (Order_ID),

    constraint Order1_FK 
        foreign key (Customer_ID) references Customer_tab(Customer_ID)
);

and data that I have added to that table is:

insert into Order_tab values (1010, '06/12/2017', 5);
insert into Order_tab values (1011, '06/12/2017', 1);
insert into Order_tab values (1012, '06/12/2017', 5);
insert into Order_tab values (1013, '06/12/2017', 1);
insert into Order_tab values (1014, '06/12/2017', 2);
insert into Order_tab values (1015, '06/13/2017', 5);
insert into Order_tab values (1016, '06/13/2017', 1);
insert into Order_tab values (1017, '07/20/2019', 12);
insert into Order_tab values (1018, '08/03/2018', 11);
insert into Order_tab values (1019, '01/29/2019', 6);
insert into Order_tab values (1020, '02/14/2019', 9);
insert into Order_tab values (1021, '03/24/2019', 11);
insert into Order_tab values (1022, '06/14/2019', 15);
insert into Order_tab values (1023, '10/31/2019', 7);
insert into Order_tab values (1024, '01/31/2020', 7);
insert into Order_tab values (1025, '10/05/2020', 14);
insert into Order_tab values (1026, '01/01/2020', 13);
oracle-apex oracle sql
3 Answers

order_date isn't a string but a date. So the engine tries to upcast '%2019' to a date in order execute the comparison. But '%2019' isn't a valid date representation.

Plus the comparison doesn't make sense in the way you used it, even if order_date was a string (which would be wrong). You may have wanted LIKE not =.

Anyway, use a range comparison.

SELECT count(*)
       FROM order_tab
       WHERE order_date >= to_date('2019-01-01', 'YYYY-MM-DD')
             AND order_date < to_date('2020-01-01', 'YYYY-MM-DD');

That has the advantage that it can use an index on order_date, whereas methods extracting the year from the order_date and comparing that to 2019 (as number or string) cannot.

user_96614240

You can use the following filter:

to_char(order_date, 'yyyy') = '2019'

Or a better one:

order_date between to_date('2019.01.01', 'yyyy.mm.dd') and to_date('2019.12.31', 'yyyy.mm.dd')
user_129305520

You are comparing a date (in the proper, DATE data type) to a string. Oracle will attempt to convert your string '%2019' to a date - which, obviously, doesn't make sense.

To fix this: EXTRACT(YEAR FROM <date_expression>) returns the year in NUMBER data type, which you can then compare to 2019 (not to a string!) There are other ways, but this is probably the simplest.

Admin1
Related