Compare with earlier date in oracle

Asked

Viewed 3,542 times

2

Hello,

I need to check which purchases were made in the last 7 days

select * from carros car  where to_date(to_char(to_date(car.datacompra,'yyyymmdd'),'dd-mm-yyyy')) = trunc(sysdate - 7)

returns me the following error:

ORA-01840: valor de entrada não é longo o suficiente para formato de data

Thank you

sql oracle

  • What kind of field datacompra ?

  • is number... yyyymmdd but is number

  • I tested it here and it worked.. how big the field is ?

  • the size of the field is number(8,0)

2 answers

2


to_date expects to receive a char, if your field is of type Number, then you need to parse number to char first.

select * from carros car  where to_date(to_char(to_date(to_char(car.datacompra),'yyyymmdd'),'dd-mm-yyyy')) = trunc(sysdate - 7)

Source : TO_DATE documentation

Issue 1

My first correction proposal was not adequate, I think this query will better meet your needs:

select * from carros car where to_date(LPAD(to_char(car.datacompra), 8,'0'),'yyyymmdd')= trunc(sysdate - 7)

I removed some transformations, and added the LPAD, to ensure that the to_date will work, preventing the error from occurring.

Documentation LPAD

  • 1

    Thank you.. but the same error appears

  • beginner, what’s the result of this for you: select * from cars Where car.datacompra between trunc(sysdate - 7) and sysdate+1

  • In my case I have to convert to date because my data type is number... but the same error occurs

  • You can run a select to check if there are values in a purchase with less than 8 characters?

  • You can try to run the following select, the LPAD will ensure that you will have an 8-character string, and in theory should solve the error, but this will not guarantee the integrity of the result. select * from cars car Where to_date(LPAD(to_char(car.datacompra), 8,'0'),'yyyymmdd')= trunc(sysdate - 7)

0

And if you do it simply:

SELECT *
  FROM carros car
 WHERE TO_DATE(car.datacompra,'YYYY-MM-DD') =
       TRUNC(SYSDATE-7)

  • Thank you... Same mistake....

  • How many records are in the table?

  • has 480 records

  • I think there should be registration in the wrong format.... order and check for us? Do not select the Where clause

  • 1

    that’s right.... record with with field not properly filled in

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.