Solved

Oracle - Date SQL calcs

Posted on 2011-02-21
4
322 Views
Last Modified: 2012-08-14
Looking for a "between" statement that will go back 30 days from yesterday's date and end at yesterday's date.

Example if the statement ran today:
...where full_date between ('1/21/2011' and '2/20/11')



0
Comment
Question by:carsRST
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34943988
where full_date between trunc(sysdate)-31 and trunc(sysdate)-1


if you want to include all time of yesterday then you'll have to include the
time piece in the between

where full_date between trunc(sysdate)-31 and trunc(sysdate)-1 + (86399/86400)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34944010
or use >=  <=


where full_date >=trunc(sysdate)-31 and
full_date <= trunc(sysdate)-1 + (86399/86400)

or

where full_date >=trunc(sysdate)-31 and
full_date <= trunc(sysdate)  - (1/86400)

or



where full_date >=trunc(sysdate)-31 and
full_date < trunc(sysdate)     -----  note <  not <=


0
 
LVL 16

Author Closing Comment

by:carsRST
ID: 34944019
sdstuber = great person

Thanks!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34944027
and finally, one more option...


where full_date between trunc(sysdate)-31 and trunc(sysdate)- (1/86400)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question