Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle SQL - Dynamic Dates

Posted on 2011-03-24
4
Medium Priority
?
355 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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

879 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