Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

Use a between to query a date range in Oracle

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
newtoperlpgm
Asked:
newtoperlpgm
  • 4
  • 4
  • 2
  • +2
1 Solution
 
newtoperlpgmAuthor Commented:
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
 
DOSLoverCommented:
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
 
PaulCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
PaulCommented:
& 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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
newtoperlpgmAuthor Commented:
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
 
newtoperlpgmAuthor Commented:
Let me give some sample dates as well, to help.  Thanks!

MyBeginDate = '10/01/2011 04:00'
MyEndDate = '10/31/2011 17:08'
0
 
PaulCommented:
@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
 
Geert GOracle dbaCommented:
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
 
newtoperlpgmAuthor Commented:
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
 
PaulCommented:
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
 
DOSLoverCommented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now