Solved

Use a between to query a date range in Oracle

Posted on 2013-06-12
13
497 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 36

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

708 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

16 Experts available now in Live!

Get 1:1 Help Now