We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Between Dates

GRChandrashekar
on
Medium Priority
337 Views
Last Modified: 2012-06-27
Dear Experts

I have the following query

SELECT transactionmonth, ADD_MONTHS(TO_DATE(transactionmonth, 'MONTH-yyyy'), 1) nextmonth
  FROM monthlybill
 WHERE     CREATEDDATE >= NVL(TRUNC(TO_DATE('1/04/2011', 'DD/MM/YYYY'), 'mm'),
                             (SELECT MIN(TO_DATE(transactionmonth, 'MONTH-yyyy'))
                                FROM monthlybill
                               WHERE employee_id = 4)
                            )
       AND CREATEDDATE < ADD_MONTHS(NVL(TRUNC(TO_DATE('1/04/2011', 'DD/MM/YYYY'), 'mm'),
                                       (SELECT MIN(TO_DATE(transactionmonth, 'MONTH-yyyy'))
                                          FROM monthlybill
                                         WHERE employee_id = 4)
                                      ),
                                   1
                                  )
       AND employee_id = 4;


Instead of using  
CREATEDDATE >=
AND CREATEDDATE <

I need to change this to between dates concept. Tried to do it but getting into error sql
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> I need to change this


there is no "need" in fact,  if you change it to BETWEEN, the optimizer will simply rewrite it for you as >= <=

also note,  between is not the same functionality as the original date clauses

if you use BETWEEN then you must subtract 1 second (1/86400)  from the end date in order to be correct
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.