Comparing varchar date with sysdate

Question

I am trying to query sum_date WHERE sum_date BETWEEN (sysdate-1) AND (sysdate-30)

In my SELECT statement I have tried TO_DATE(sum_date, 'DD-MM-YY') and sysdate

But it throws error:

"literal does not match format string."

Sum_date is stored as varchar20 and looks like 2014-01-07.
sysdate looks like 18-SEP-14.

Try this TO_DATE(sum_date, 'yyyy-mm-dd') – Aramillo Sep 18 '14 at 20:35 — Aramillo, Sep 18 '14 at 20:35

Mureinik · Answer 1 · 2014-09-19T10:23:17.723

3

Your format is 'YYYY-MM-DD', so:

SELECT *
FROM   my_table
WHERE  TO_DATE(sum_date, 'YYYY-MM-DD') 
       BETWEEN (sysdate - 30) AND (sysdate - 1)

edited Sep 19 '14 at 10:23

answered Sep 18 '14 at 20:37

Mureinik

297,002
52
306
350

score 0 · Answer 2 · answered Jul 12 '16 at 16:19

You can also change the NLS_DATE_FORMAT of your session to match your need:

alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD';

create table y 
  ( y varchar2(10));  
  select y.*, rowid from y
;

select * from y where y between sysdate -10000 and sysdate +100000;
select * from y where to_date(y,'YYYY-MM-DD') between '2010-04-01' and '2016-04-01' ;

Comparing varchar date with sysdate

2 Answers2