Between Dates

Posted on 2011-04-26
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)
                                       (SELECT MIN(TO_DATE(transactionmonth, 'MONTH-yyyy'))
                                          FROM monthlybill
                                         WHERE employee_id = 4)
       AND employee_id = 4;

Instead of using  

I need to change this to between dates concept. Tried to do it but getting into error sql
Question by:GRChandrashekar
    LVL 42

    Accepted Solution

    CREATEDDATE >= ....
    AND CREATEDDATE <= ...   (note the "=" sign, which I  added

    is equivalent to the between function.  But if you insist on using between, then

    it's:    CREATEDATE BETWEEN .... and ....

    LVL 73

    Expert Comment

    >>> 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

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now