Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Date Clause

Posted on 2010-11-30
13
Medium Priority
?
222 Views
Last Modified: 2012-05-10
I need the following query to altered

select *
from BLOCKMEMBER
where trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO

Without disturbing the above I want check one more cndition
if EFFECTIVEDATEFROM is less than sysdate and if EFFECTIVEDATETO is null then it should appear in output of query.

if EFFECTIVEDATEFROM is greater than sysdate and if EFFECTIVEDATETO is null then it should NOT appear in output of query.

and I need to conver this stored procedure with input MEMBER_ID OR EMPLOYEE_ID OR CLUB_ID
0
Comment
Question by:GRChandrashekar
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34237597
what about this:
select *
from BLOCKMEMBER
where trunc(sysdate) between EFFECTIVEDATEFROM and NVL(EFFECTIVEDATETO, sysdate)

Open in new window

0
 

Author Comment

by:GRChandrashekar
ID: 34237610
Works ok but if EFFECTIVEDATEFROM is equal to sysdate then it is not coming in output. it should come
0
 
LVL 12

Expert Comment

by:enachemc
ID: 34237611
select *
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (EFFECTIVEDATEFROM < trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID)


p_ are paremeters
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:GRChandrashekar
ID: 34237620
angelIII:
enachemc:

Works ok but if EFFECTIVEDATEFROM is equal to sysdate then it is not coming in output. it should come
0
 
LVL 1

Expert Comment

by:sunny25
ID: 34237624
I hope this fulfils your requirement
select *
from BLOCKMEMBER
where trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or
(EFFECTIVEDATEFROM <TRUNC(sysdate) AND EFFECTIVEDATETO IS NULL )
0
 

Author Comment

by:GRChandrashekar
ID: 34237635
sunny25:
Works ok but if EFFECTIVEDATEFROM is equal to sysdate then it is not coming in output. it should come
0
 
LVL 12

Expert Comment

by:enachemc
ID: 34237637
select *
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (EFFECTIVEDATEFROM <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID)
0
 

Author Comment

by:GRChandrashekar
ID: 34237644
enachemc: Stil not working
0
 

Author Comment

by:GRChandrashekar
ID: 34237649
I have given effectivefrom date as 30/11/2010 09:00:00 PM which is sysdate and effective to is null
0
 
LVL 12

Expert Comment

by:enachemc
ID: 34237659
select *
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (trunc(EFFECTIVEDATEFROM) <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID)
0
 
LVL 1

Expert Comment

by:sunny25
ID: 34237664
try this
EFFECTIVEDATEFROM <=TRUNC(sysdate) AND EFFECTIVEDATETO IS NULL
0
 

Author Comment

by:GRChandrashekar
ID: 34237671
@ enachemc:

This is perfect.
Request please give me stored procedure to pass date instead of sysdate and to pass MEMBER_ID OR
EMPLOYEE_ID
OR
CLUB_ID
0
 
LVL 12

Accepted Solution

by:
enachemc earned 2000 total points
ID: 34237713
create or replace procedure prod(dt date, p_MEMBER_ID number, p_EMPLOYEE_ID number, p_CLUB_ID number)
res number;
begin
select id into re into
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (trunc(EFFECTIVEDATEFROM) <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID);

--if you want only one of the IDs, just make the others NULL in the calling sql
end prod;
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

885 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