Oracle SQL - Dynamic Dates

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'
LVL 16
carsRSTAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
elect * from my_table where my_date
between add_months(trunc(sysdate,'yyyy') ,-1) and trunc(sysdate)

0
 
sdstuberCommented:
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
 
carsRSTAuthor Commented:
You're a good man!

Thanks.
0
 
carsRSTAuthor Commented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.