Home » SQL & PL/SQL » SQL & PL/SQL » How to select records depending on date and time values?
How to select records depending on date and time values? [message #282841] Sat, 24 November 2007 01:51 Go to next message
venkatadeekshi
Messages: 17
Registered: October 2007
Junior Member
Hi,
I hava a table with the following data.

SEAT_ID CAL_DAY FROM_TIME TO_TIME
1049 11/15/2007 00:30 04:59
1049 11/15/2007 11:00 23:59
1049 11/16/2007 00:30 04:59
1049 11/16/2007 11:00 23:59
1049 11/17/2007 00:30 04:59
1049 11/17/2007 11:00 23:59
1049 11/18/2007 00:30 04:59
1049 11/18/2007 11:00 23:59
1049 11/19/2007 00:30 04:59
1049 11/19/2007 11:00 23:59
1049 11/20/2007 00:30 04:59
1049 11/20/2007 11:00 23:59
1049 11/21/2007 00:30 04:59
1049 11/21/2007 11:00 23:59
1049 11/22/2007 00:30 04:59
1049 11/22/2007 11:00 23:59
1051 11/15/2007 14:00 23:59
1051 11/16/2007 00:00 05:59
1051 11/16/2007 14:00 23:59
1051 11/17/2007 00:00 05:59
1051 11/17/2007 14:00 23:59
1051 11/18/2007 00:00 05:59
1051 11/18/2007 14:00 23:59
1051 11/19/2007 00:00 23:59
1051 11/20/2007 00:00 23:59
1051 11/21/2007 00:00 23:59
1051 11/22/2007 00:00 23:59
1053 11/15/2007 00:00 05:59
1053 11/15/2007 18:00 23:59
1053 11/16/2007 00:00 05:59
1053 11/16/2007 18:00 23:59
1053 11/17/2007 00:00 05:59
1053 11/17/2007 18:00 23:59
1053 11/18/2007 00:00 23:59
1053 11/19/2007 00:00 23:59
1053 11/20/2007 00:00 23:59
1053 11/21/2007 00:00 23:59
1053 11/22/2007 00:00 23:59
1054 11/15/2007 00:00 23:59
1054 11/16/2007 00:00 23:59
1054 11/17/2007 00:00 23:59
1054 11/18/2007 00:00 23:59
1054 11/19/2007 00:00 23:59
1054 11/20/2007 00:00 23:59
1054 11/21/2007 00:00 23:59
1054 11/22/2007 00:00 23:59
1055 11/15/2007 00:00 23:59
1055 11/16/2007 00:00 23:59
1055 11/17/2007 00:00 23:59
1055 11/18/2007 00:00 23:59
1055 11/19/2007 00:00 23:59
1055 11/20/2007 00:00 23:59
1055 11/21/2007 00:00 23:59
1055 11/22/2007 00:00 23:59
1080 11/15/2007 00:00 23:59
1080 11/16/2007 00:00 23:59
1080 11/17/2007 00:00 23:59
1080 11/18/2007 00:00 23:59
1080 11/19/2007 00:00 23:59
1080 11/20/2007 00:00 23:59
1080 11/21/2007 00:00 23:59
1080 11/22/2007 00:00 23:59
1100 11/15/2007 00:00 23:59
1100 11/16/2007 00:00 23:59
1100 11/17/2007 00:00 23:59
1100 11/18/2007 00:00 23:59
1100 11/19/2007 00:00 23:59
1100 11/20/2007 00:00 23:59
1100 11/21/2007 00:00 23:59
1100 11/22/2007 00:00 23:59

I am querying the table using a query

SELECT
seat_id,cal_day,from_time,to_time
FROM table_name
WHERE
cal_day BETWEEN '15-NOV-2007' AND '22-NOV-2007'
AND from_time<= '05:30'
AND to_time >= '16:00'

The output is

SEAT_ID CAL_DAY MIN_TIME MAX_TIME
1051 11/19/2007 00:00 23:59
1051 11/20/2007 00:00 23:59
1051 11/21/2007 00:00 23:59
1051 11/22/2007 00:00 23:59
1053 11/18/2007 00:00 23:59
1053 11/19/2007 00:00 23:59
1053 11/20/2007 00:00 23:59
1053 11/21/2007 00:00 23:59
1053 11/22/2007 00:00 23:59
1054 11/15/2007 00:00 23:59
1054 11/16/2007 00:00 23:59
1054 11/17/2007 00:00 23:59
1054 11/18/2007 00:00 23:59
1054 11/19/2007 00:00 23:59
1054 11/20/2007 00:00 23:59
1054 11/21/2007 00:00 23:59
1054 11/22/2007 00:00 23:59
1055 11/15/2007 00:00 23:59
1055 11/16/2007 00:00 23:59
1055 11/17/2007 00:00 23:59
1055 11/18/2007 00:00 23:59
1055 11/19/2007 00:00 23:59
1055 11/20/2007 00:00 23:59
1055 11/21/2007 00:00 23:59
1055 11/22/2007 00:00 23:59
1080 11/15/2007 00:00 23:59
1080 11/16/2007 00:00 23:59
1080 11/17/2007 00:00 23:59
1080 11/18/2007 00:00 23:59
1080 11/19/2007 00:00 23:59
1080 11/20/2007 00:00 23:59
1080 11/21/2007 00:00 23:59
1080 11/22/2007 00:00 23:59
1100 11/15/2007 00:00 23:59
1100 11/16/2007 00:00 23:59
1100 11/17/2007 00:00 23:59
1100 11/18/2007 00:00 23:59
1100 11/19/2007 00:00 23:59
1100 11/20/2007 00:00 23:59
1100 11/21/2007 00:00 23:59
1100 11/22/2007 00:00 23:59

But what i required is when i query using the date range i need to display those seats which are satisfying the time range criteria for the entire date range.

so the above output should not contain the seats 1051 and 1053 because they are
not satisfying the condition for the entire date range.

can any body suggest me a query or a procedural approach for this.

Thanks and Regards

Mantha

[TOPIC RENAMED by LF; the original title was "Solution urgently required"]

[Updated on: Sat, 24 November 2007 03:50] by Moderator

Report message to a moderator

Re: Solution urgently required [message #282847 is a reply to message #282841] Sat, 24 November 2007 03:09 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

first of all format your code.see the first post in the forum on how to format your code.
2.
Quote:

cal_day BETWEEN '15-NOV-2007' AND '22-NOV-2007'

these are not dates but strings.check the to_date function.

correct these things first and then post what you did.

regards,
Re: solution urgently required [message #282851 is a reply to message #282841] Sat, 24 November 2007 03:24 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Various things to point out.
1. Do NOT post the same question in multiple forums.
2. The query that you provided did bot return the results that you say it did, you have either altered the query or altered the posted results.
3. NEVER, EVER use the word urgent anywhere in you posts. Urgent is your issue, not ours and to imply that your problem is any more important than anyone else's on this forum is simply rude.
When posting example data, don't post just the data. Post the create table statements and the insert statements. Note, it should be possible for a forum poster to simply copy your code, paste it nto a code window and successfully run it in order to create a valid test scenario.
4. Format your code and provide the version that you are working with.
All of this info and much more can be found here
5. What on earthmakes you think that this is either a pl/sql issue OR for that matter, an experts issue. This thread belongs firmly in the newbies forum.
Re: solution urgently required [message #282858 is a reply to message #282851] Sat, 24 November 2007 04:19 Go to previous messageGo to next message
venkatadeekshi
Messages: 17
Registered: October 2007
Junior Member
Sorry for that. This is my first post in this forum.

Thanks and regards
Re: solution urgently required [message #282878 is a reply to message #282858] Sat, 24 November 2007 09:01 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Being a newbie isn't excuse for not reading the OraFAQ Forum Guide.

./fa/3442/0/
Re: How to select records depending on date and time values? [message #282919 is a reply to message #282841] Sun, 25 November 2007 02:08 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,

Can you post the structure of the table?

Thanks,
Nirav
Previous Topic: Caching static data
Next Topic: validation in sql loader
Goto Forum:
  


Current Time: Tue Dec 06 16:18:13 CST 2016

Total time taken to generate the page: 0.12740 seconds