Solved

Oracle SQL - Dynamic Dates

Posted on 2011-03-24
4
351 Views
Last Modified: 2012-05-11
Looking for SQL to create dynamic dates, so that it always pulls from December 1 of prior year to the current date.

Example (if run today):
Select * from my_table where my_date between '12/1/2010' and '3/24/2011'

Need this part to be dynamic: '12/1/2010' and '3/24/2011'
0
Comment
Question by:carsRST
  • 2
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 35208660
elect * from my_table where my_date
between add_months(trunc(sysdate,'yyyy') ,-1) and trunc(sysdate)

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35208669
note the "between" will exclude all data for today unless my_date is for midnight  00:00:00


if you want to include today, try one of these clauses instead

my_date between add_months(trunc(sysdate,'yyyy') ,-1) and trunc(sysdate)+1 - 1/86400


or

my_date >=  add_months(trunc(sysdate,'yyyy') ,-1) and my_date < trunc(sysdate)+1



0
 
LVL 16

Author Closing Comment

by:carsRST
ID: 35208670
You're a good man!

Thanks.
0
 
LVL 16

Author Comment

by:carsRST
ID: 35208679
>>note the "between" will exclude all data for today unless my_date is for midnight  00:00:00

Actually, I really need as of the previous day anyway.  works out perfectly.

Thank you!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

820 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