Using Between two Dates.

[Rajiya]

Hi, I have written a query to extract data from a table whose created date lies between current date and 30 days ago.in PL/SQL I have written query as follows: select creat_dt from shpt where shpt.creat_dt between trunc(sysdate) and trunc(sysdate-30) Although it is having records for which created date falls between current date and 30 days prior to current date, it still shows me no records extracted. Help me with this.

[Ashfield]

Don't know about Oracle, but in SQL Server that wouldn't work, you have to put the earliest date first.

where shpt.creat_dt between trunc(sysdate-30) and trunc(sysdate)

It's not logical, but thats how it works in SQL Server and I suspect Oracle!

Bob Ashfield Consultants Ltd

[Wendelius]

Yap, this applies to Oracle also. Based on ANSI standard between is converted to: Field >= StartValue AND Field <= EndValue Logical, isn't it :-D

[nelsonpaixao]

hi, try swap the dates between 20-10-2000 and 25-10-2001 between 25-10-2001 and 20-10-2000 it has to work no reason for otherwise:~

nelsonpaixao@yahoo.com.br trying to help & get help