OraFAQ Forum: SQL & PL/SQL » query start_date and end

Home » SQL & PL/SQL » SQL & PL/SQL » query start_date and end_Date (windows Server 2008 r2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query start_date and end_Date [message #648009]

Sun, 14 February 2016 23:55

Dips99
Messages: 6
Registered: February 2016
Location: Dehli

Junior Member

Hello experts,

I want to display my query result based on the start_date and end_date column in my table.
As displayed in the image. Like if i pass the id and start_Date and end_Date in where clause then, the result should be display for that record who lies between and equal to passed parameters in where clause.
like start_Date and end_date column should be treated as date range.
below is the structure.....

Id	driverid	sys_user_id	sys_service_id	start_date	end_date	driver_name 
2	30		2372		3171		2016-02-17	2016-02-18	Sunil 
4	31		2372		3171		2016-02-19	2016-02-25	Sharad 
5	87		2372		3171		2016-02-20	2016-02-28	gorakh 
6	118		2372		3171		2016-02-29	2016-02-29	GNM 
8	88		2372		5174		2016-02-10	2016-02-16	gorakh 
9	70		3156		5929		2016-02-10	2016-02-16	Mr.RAMA SHANKER PANDEY

like if i am passing the parameter sys_service_id='5174'
and start_date is '2016-02-10' and end_Date is '2016-02-12'
then row corresponding to 5174 sys_service_id must be display...

Can anyone help me regarding this...

[EDITED by LF: applied [code] tags & fixed formatting]
/forum/fa/13023/0/

[mod-edit: image inserted into messaged body by bb]

Attachment: Assig_driver.jpg
(Size: 36.46KB, Downloaded 1207 times)

[Updated on: Mon, 15 February 2016 13:25] by Moderator

Report message to a moderator

Re: query start_date and end_Date [message #648012 is a reply to message #648009]

Mon, 15 February 2016 00:00

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It doesn't seem to be too difficult. What did you try so far? Share your own query with us, someone will assist in fixing it.

Report message to a moderator

Re: query start_date and end_Date [message #648014 is a reply to message #648009]

Mon, 15 February 2016 00:47

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: query start_date and end_Date [message #648024 is a reply to message #648012]

Mon, 15 February 2016 02:46

Dips99
Messages: 6
Registered: February 2016
Location: Dehli

Junior Member

help me regarding this query to short out the issue..
if, I am using this query then it is showing me the rocord corrosponding to that id
as shown below..

select * from assign_driver where sys_service_id='5174' and
(start_date between '2016-02-01' and '2016-02-20'
or end_date between '2016-02-01' and '2016-02-20')

but if i am changing the date parameters then result is nothing. query is

select * from assign_driver where sys_service_id='5174' and
(start_date between '2016-02-11' and '2016-02-12'
or end_date between '2016-02-11' and '2016-02-12')

So, i want to act start_date and end_Date as date range..
suppose if i will pass start_date and end_date then it will check that it lies between the dates in the record. if it lies between the start_date and end_date in reocord.

then must return that row.

help me to short out this

Thanks,

Report message to a moderator

Re: query start_date and end_Date [message #648025 is a reply to message #648024]

Mon, 15 February 2016 02:53

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Would

where ...
  and :par_your_date between start_date and end_date

do any good?

By the way, do use DATES, not STRINGS ('2016-02-11' is a string, not a date. See TO_DATE function).

EDIT:

I guess I should explain what I meant.

You said "So, i want to act start_date and end_Date as date range". If these dates (from the ASSIGN_DRIVER) represent a range, then you could enter only ONE date as a parameter and get a record whose <start date - end date> range contains the parameter's value.

[Updated on: Mon, 15 February 2016 03:00]

Report message to a moderator