Solved

Use a between to query a date range in Oracle

Posted on 2013-06-12
13
503 Views
Last Modified: 2013-07-18
Hello,
I am using the following query  to select records that have a timestamp,(06/11/2013 08:15:22) thanks to experts on here who helped getting this to work.

select * from mytable
where mydate >= to_date(  parameter ,'mm/dd/yyyy')
and mydate  < to_date( parameter ,'mm/dd/yyyy') + 1

Now I need to somehow use a between in order to query records within a date range.
For example,

I have two parameters for mydate -- mybegindate and myenddate and I want all records between mybegindate and myenddate.

Thanks for your help.
0
Comment
Question by:newtoperlpgm
  • 4
  • 4
  • 2
  • +2
13 Comments
 

Author Comment

by:newtoperlpgm
ID: 39242992
Oh, and by the way, another detail, I want the date range to be optional, which means that users may enter either one date, or a date range, and the query still work....
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39243036
Pleaset try this. Please not that if parameter dates are NULL, it defaults to current system dates. With 'between', the dates are inclusive. Also, I am using TRUNC on the date to ignore time value.
Select * from mytable
 where trunc(mydate) between to_date(NVL(trunc(mybegindate),SYSDATE)) and to_date(NVL(trunc(myenddate),SYSDATE))  

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39243126
don't use the sql "between.... and" construct

the code in your question is MORE RELIABLE than between!
for more on this topic please see: "Beware of Between"

please don't be beguiled into thinking that the sql "between" is better than what you have - it isn't

regarding the optional parameters - how are you collecting them (e.g. in a web form?)
and how are you executing the query (e.g. from java?)

or are you simply wishing to make that query into a stored procedure (with 2 date parameters)?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39243702
I totally agree with PortletPaul regarding the use of BETWEEN ;-)
Depending on how your sql statement will be "modified" by user parameters and how & where you're executing that statement, what about using a (pipelined) table function with desired parameters?!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39243728
& I agree with Alex140181 on pipelined table functions :) although I did mention an sp
(I think table functions are too often overlooked as a viable option)
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39243752
For some reference & samples regarding table functions, you may look here

http://www.oracle-base.com/articles/misc/pipelined-table-functions.php

http://psoug.org/reference/pipelined.html

... or simply ask, if you need help with that ;-)
0
 

Author Comment

by:newtoperlpgm
ID: 39244748
What I am trying to do is the following:

I have a date range to be used in a query, where people will enter two dates, mybegindate and myenddate.
I want to select all records that are within that date range. I don't necessarily need to use between, but I need to query for all records that are within the date range.  The query above in my original post is the query I am now using.  So far, what I've done is create the two new fields.  So, I can query for

select * from mytable
where mydate >= to_date(  parameter ,'mm/dd/yyyy')
and mydate  < to_date( parameter ,'mm/dd/yyyy') + 1
or
select * from mytable
where "the date range is between mybegindate and myenddate"  (this is the part I'm seeking help with)
Thanks!
0
 

Author Comment

by:newtoperlpgm
ID: 39244904
Let me give some sample dates as well, to help.  Thanks!

MyBeginDate = '10/01/2011 04:00'
MyEndDate = '10/31/2011 17:08'
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39246378
@newtoperlpgm

Q1: do you need to take the times into account?
  i.e. only data that
          occurs on or after 04:00 Oct 10 2011
      and
          occurs before 17:08 Oct 31 2011

       Q1-1: or: do you want 17:08 included?

Q2: or do you want to filter by the days on which those times occur?
  i.e. only data that
          occurs on or after Oct 10 2011
      and
          occurs on and during Oct 31 2011 (i.e. before Nov 1 2011)

Please identify how you are passing these optional parameters into the query. Are you using C#? PHP? Java? (something else?)

NB: most "date controls" on forms don't provide time of day, hence it can be awkward for users to enter date filters to minute precision.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39246981
your describing a programming problem for a front-end app and the translation towards a query

when providing a user with a option for selecting a specific date or a date range
it's easier to first provide a option item to indicate what the user wants:
 date picker with options
based on the option chosen build the query
(in pseudo code)
sql = 'SELECT * FROM TABLE '
if option = specific
(
  sql = sql + 'WHERE MYDATE = :MYDATE'
  sql.params('MYDATE') = dtpSpefic.Date
)else
(
  sql = sql + 'WHERE MYDATE >= :MYDATE_FROM and MYDATE <:MYDATE_TO'
  sql.params('MYDATE_FROM') = dtpFrom.Date
  sql.params('MYDATE_TO') = dtpTo.Date
)

Having a specifc query for all possible solutions usually leads to performance problems
0
 

Author Comment

by:newtoperlpgm
ID: 39291818
Sorry for the delay, I was out of town.  I was able to figure out my query....I am going to post it for review, because I am wondering if there is a better way....thanks very much for your help.

SELECT ITEMID FROM VW_ITEMVIEW
where VW_ITEMVIEW.ITEMID = PARAMETER1
OR VW_ITEMVIEW.SUBMITTEDBY = PARAMETER2
OR VW_ITEMVIEW.TA like PARAMETER3
OR VW_ITEMVIEW.LOGIN_DATE >= to_date(PARAMETER_LOGIN_DATE, 'mm/dd/yyyy')
and VW_ITEMVIEW.LOGIN_DATE < to_date(PARAMETER_LOGIN_DATE, 'mm/dd/yyyy') + 1
OR (LOGIN_DATE >= TO_DATE(PARAMETER_LOGIN_DATE_BEGIN, 'MM/DD/YYYY')
 AND LOGIN_DATE <= TO_DATE(PARAMETER_LOGIN_DATE_END, 'MM/DD/YYYY'))
OR (LOGIN_DATE between TO_DATE(PARAMETER_LOGIN_DATE_BEGIN, 'MM/DD/YYYY')
 AND TO_DATE(PARAMETER_LOGIN_DATE_END, 'MM/DD/YYYY'))
order by ITEMID, to_number(regexp_replace(ITEMID,'[^0-9]'))
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39292031
I suggest this:
SELECT ITEMID
FROM VW_ITEMVIEW
WHERE  (
        VW_ITEMVIEW.ITEMID = PARAMETER1
            AND PARAMETER1 IS NOT NULL
       )
    OR (
        VW_ITEMVIEW.SUBMITTEDBY = PARAMETER2
            AND PARAMETER2 IS NOT NULL
       )
    OR (
        VW_ITEMVIEW.TA LIKE PARAMETER3
            AND PARAMETER3 IS NOT NULL
       )
    OR (
        VW_ITEMVIEW.LOGIN_DATE >= to_date(PARAMETER_LOGIN_DATE, 'mm/dd/yyyy')
            AND VW_ITEMVIEW.LOGIN_DATE < to_date(PARAMETER_LOGIN_DATE, 'mm/dd/yyyy') + 1
            AND PARAMETER_LOGIN_DATE IS NOT NULL
        )
    OR (
        LOGIN_DATE >= TO_DATE(PARAMETER_LOGIN_DATE_BEGIN, 'MM/DD/YYYY')
            AND LOGIN_DATE <  TO_DATE(PARAMETER_LOGIN_DATE_END, 'MM/DD/YYYY') + 1
            AND PARAMETER_LOGIN_DATE_END IS NOT NULL
        )
ORDER BY ITEMID
    , to_number(regexp_replace(ITEMID, '[^0-9]'))

Open in new window

compare it to your existing (formatted)
SELECT ITEMID
FROM VW_ITEMVIEW
WHERE VW_ITEMVIEW.ITEMID = PARAMETER1
	OR VW_ITEMVIEW.SUBMITTEDBY = PARAMETER2
	OR VW_ITEMVIEW.TA LIKE PARAMETER3
	OR VW_ITEMVIEW.LOGIN_DATE >= to_date(PARAMETER_LOGIN_DATE, 'mm/dd/yyyy')
	AND VW_ITEMVIEW.LOGIN_DATE < to_date(PARAMETER_LOGIN_DATE, 'mm/dd/yyyy') + 1
	OR (
		LOGIN_DATE >= TO_DATE(PARAMETER_LOGIN_DATE_BEGIN, 'MM/DD/YYYY')
		AND LOGIN_DATE <= TO_DATE(PARAMETER_LOGIN_DATE_END, 'MM/DD/YYYY')
		)
	OR (
		LOGIN_DATE BETWEEN TO_DATE(PARAMETER_LOGIN_DATE_BEGIN, 'MM/DD/YYYY')
			AND TO_DATE(PARAMETER_LOGIN_DATE_END, 'MM/DD/YYYY')
		)
ORDER BY ITEMID
	, to_number(regexp_replace(ITEMID, '[^0-9]'))

Open in new window

In your existing code you have 2 functionally identical blocks lines 8-11 and 9-15 (between IS >= and <=, it's the same thing).

I have introduced >= and < instead (this really is a better method) see line 22 in particular
 please see: "Beware of Between"
and if you don't believe me try this:
"Bad habits to kick : mis-handling date / range queries"

I also suspect you may need IS NULL tests on the various parameters
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39292093
At the begnning, you said "I want the date range to be optional, which means that users may enter either one date, or a date range". Now I see three parameter dates - PARAMETER_LOGIN_DATE, PARAMETER_LOGIN_DATE_BEGIN, PARAMETER_LOGIN_DATE_END. Does the query need to determine which of these dates to use based on if any of these are NULLs? For example, if PARAMETER_LOGIN_DATE is NOT NULL, then we compare table's login-date to that date. If it is NULL, do we need to assume both PARAMETER_LOGIN_DATE_BEGIN and PARAMETER_LOGIN_DATE_END are Not Nulls before comparing (for 'between' or its equivalent). ?
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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 video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

821 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