[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Between Dates

Posted on 2011-04-26
2
Medium Priority
?
330 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
0
Comment
Question by:GRChandrashekar
2 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 2000 total points
ID: 35472631
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 ....





0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35472791
>>> 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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

872 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