June 20, 2008 at 9:47 am
OK my guru friends,
I have a little problem, how can set a date as a wild card. Example -
I have nulls in the date columns, and user needs to test on one date column or the other date column.
Invoice date -
Completed Date -
Paid Date -
Reject Date -
Approve Date -
Please help. .
June 20, 2008 at 9:55 am
Read up on COALESCE in BOL.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 20, 2008 at 10:37 am
And show some work in what you think or have attempted.
June 20, 2008 at 11:06 am
I have created this procedure with only 2 date columns, but I get nulls and all the records when I enter a date and it wont narrow it down to the dates I entered.
ALTER PROCEDURE mr_test_COALESCE
--DECLARE
@PostingDateF DateTime,
@PostingDateT DateTime,
@dtDueDatefDateTime,
@dtDueDateT DateTime
AS
set @PostingDateF = null
set @PostingDateT = null
set @dtDueDatef= null
set @dtDueDateT = null
select *
from InvoiceHeader
where
dtDueDate
Between COALESCE(@dtDueDatef,dtDueDate)
ANDCOALESCE(@dtDueDatet,dtDueDate)
OR
dtPostingDate
Between COALESCE(@PostingDateF,dtPostingDate)
ANDCOALESCE(@PostingDateT,dtPostingDate)
June 20, 2008 at 11:15 am
I must be missing something. The first thing you do in the proc is null the input parameters. Doesn't that kind of defeat the whole purpose of input parameters?
I think this will do what you're looking for:
create PROCEDURE mr_test_input
(@PostingDateF_in DateTime,
@PostingDateT_in DateTime,
@DueDateF_in DateTime,
@DueDateT_in DateTime)
as
select *
from InvoiceHeader
where dtDueDate between @DueDateF_in and @DueDateT_in
or dtPostingDate between @PostingDateF_in and @PostingDateT_in
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 12:23 pm
I need to be able to enter a date for InvoiceDate and leave the other alone or enter a date for PaidDate and leave the others alone.
June 20, 2008 at 1:01 pm
maynor_ramirez (6/20/2008)
I need to be able to enter a date for InvoiceDate and leave the other alone or enter a date for PaidDate and leave the others alone.
That's what the "or" statement does.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 1:30 pm
I guess I cannot leave the date blank if I do want to test on it.
With the Or I have to pick a date for any datetime column, but what I'm trying to do is either enter a date or leave it blank, that's what I asked if it was possible to put a datetime field as wild card.
My original request came with 7 different parameters
accountid range
lastName,
VendorName,
accountingdate,
postingdate,
approveddate,
and they want to test on one or two or more,
I dont know if makes sense,
June 20, 2008 at 1:50 pm
Taking what GSquared gave you and adding to it - I think you want this:
CREATE PROCEDURE mr_test_input
@PostingDateF_in DateTime = NULL
,@PostingDateT_in DateTime = NULL
,@DueDateF_in DateTime = NULL
,@DueDateT_in DateTime = NULL
AS
IF (@PostingDateF_in IS NOT NULL AND @PostingDateT_in IS NULL)
BEGIN
RAISERROR('@PostingDateT_in is required when @PostingDateF_in is specified.', 16, 1);
RETURN;
END;
IF (@DueDateF_in IS NOT NULL AND @DueDateT_in IS NULL)
BEGIN
RAISERROR('@DueDateF_in is required when @DueDateT_in is specified.', 16, 1);
RETURN;
END;
SELECT *
FROM InvoiceHeader
WHERE (@DueDateF_in IS NOT NULL
AND dtDueDate >= @DueDateF_in
AND dtDueDate < @DueDateT_in)
OR (@PostingDateF_in IS NOT NULL
AND dtPostingDate >= @PostingDateF_in
AND dtPostingDate < @PostingDateT_in)
It is generally not a good idea to use BETWEEN with dates. Also, with this it will be limited by the time that is passed in also, so passing in '2008-06-20 10:00' for @PostingDateF_in will exclude all invoices that have a date less than 10am today.
Another approach would be to identify what kind of search you want (Due date or Posting Date) and only use two date input parameters:
CREATE PROCEDURE mr_test_input
@SearchType varchar(11)
,@beginDate datetime
,@endDate datetime
AS
SELECT *
FROM InvoiceHeader
WHERE (@SearchType = 'DueDate'
AND dtDueDate >= @beginDate
AND dtDueDate < @endDate)
OR (@SearchType = 'PostingDate'
AND dtPostingDate >= @beginDate
AND dtPostingDate < @endDate);
HTH,
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2008 at 2:25 pm
maynor_ramirez (6/20/2008)
I guess I cannot leave the date blank if I do want to test on it.With the Or I have to pick a date for any datetime column, but what I'm trying to do is either enter a date or leave it blank, that's what I asked if it was possible to put a datetime field as wild card.
My original request came with 7 different parameters
accountid range
lastName,
VendorName,
accountingdate,
postingdate,
approveddate,
and they want to test on one or two or more,
I dont know if makes sense,
With the "or" statement, all you need to do is set the parameter you don't want to search for as null.
For example, if you want to look for the Posting Date, but not the Due Date, you would use this:
exec dbo.mr_test_input
@PostingDateF_in = '1/1/2008',
@PostingDateT_in = 1/31/2008',
@DueDateF_in = null,
@DueDateT_in = null
That would find the ones where the Posting Date was between 1 Jan and 31 Jan.
As pointed out by another person, you might want to modify the code slightly to make it look for anything up to the midnight that follows the To date.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 25, 2008 at 3:14 pm
I just have to say that you guys are great, I really apreciate all your help -
SQLServerCentral Rocks!!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply