Getting not valid month in Oracle sql

Question

I have a table called Transactions that has a column called trans_date. I am just trying to do a simple query in the SQL*Plus command window

The query is

SELECT * FROM transactions WHERE 
trans_date BETWEEN to_date('09/11/2021','mm/dd/yyyy') AND to_date('09/12/2021','mm/dd/yyyy');

When I run this query I get not valid month and there is a little * under trans_date. Most of what I have read suggests the query is right but I am not sure what the problem is. The data type is varchar2(20).

Perhaps the data type of the trans_date column is wrong? Can't really tell from the information you've given. — Dawood ibn Kareem, Sep 12 '21 at 05:18
Why is it varchar2 and not date? That will make everything harder and make all kinds of bugs possible. — William Robertson, Sep 12 '21 at 10:08

score 1 · Accepted Answer · answered Sep 12 '21 at 05:31

Since trans_date is a varchar and you're trying to query whether it's between two dates, you need to convert it to a date too. Assuming it has the same format as the literals in your query:

SELECT * 
FROM   transactions 
WHERE  to_date(trans_date, 'mm/dd/yyy') BETWEEN 
       to_date('09/11/2021','mm/dd/yyyy') AND to_date('09/12/2021','mm/dd/yyyy');

score 0 · Answer 2 · answered Sep 12 '21 at 05:34

0

Seems like problem is columns data type, Try convert it to date,

SELECT * FROM transactions 
WHERE to_date(trans_date,'mm/dd/yyyy') BETWEEN to_date('09/11/2021','mm/dd/yyyy') AND to_date('09/12/2021','mm/dd/yyyy');

answered Sep 12 '21 at 05:34

ray

1,512
4
11

score 0 · Answer 3 · answered Sep 12 '21 at 11:19

You need to convert trans_date to a date. However, you can use date constants for the comparisons:

SELECT *
FROM transactions
WHERE to_date(trans_date, 'mm/dd/yyyy') BETWEEN DATE '2021-09-11' AND DATE '2021-09-12';

You should fix your data model so the dates are stored correctly, using Oracle's built-in data type.

Getting not valid month in Oracle sql

3 Answers3