Stored Procedure - Start and end date, looking at today's date

0
votes

I have a table with this entries:

StartDate     |     EndDate
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-06    |     2016-01-16
2016-01-07    |     2016-01-17
2016-01-07    |     2016-01-17
2016-01-07    |     2016-01-17
2016-01-07    |     2016-01-17
2016-01-07    |     2016-01-17
2016-01-07    |     2016-01-17
2016-01-07    |     2016-01-17
2016-01-07    |     2016-01-17
2016-01-08    |     2016-01-18
2016-01-08    |     2016-01-18
2017-01-01    |     2017-01-10
2026-01-06    |     2026-01-16
2026-01-07    |     2026-01-17

And my query in stored procedure is:

SELECT ROW_NUMBER() OVER ( ORDER BY [StartDate] ASC) as ROWNUM, [Oid] 
From [dbo].[Ument] 
Where (
        (@StartDate is null or StartDate >= @StartDate)
        or (@EndDate is null or EndDate <= @EndDate)
      )

I want to search by @StartDate written by user or, if is null/empty, by @StartDate >= today's date.

But, at the same time, I want to search by @endDate, written by user or, if is null/empty, EndDate >= today's.

If today's is between two dates, i need to return it.

Like this:

@startDate isn't null? YES: use the @startDate written by user to filter startDate >= @StartDate. NO: use today's date to put startDate >= today.

@endDate isn't null? YES: use the @endDate written by user to filter endDate <= @endDate NO: use max-date (e.g.: 2050/12/31) to filter endDate <= max-Date

3
sql-serverdatestored-procedures
What is an output from your sql? what is your desire output? – Hiten004
I didn't understand the question, but I think second "OR" (..*or* (@EndDate..) should be "AND" in your criteria. – Ramazan Binarbasi
Sorry, just put the stored procedure so far as it had the doubt. – Nuno Ribeiro

3 Answers

4
votes

Is this what you want?

WHERE startDate >= COALESCE(@StartDate, GETDATE()) AND
      endDate <= COALESCE(@EndDate, GETDATE())

You may want this logic, but with the current date with no time:

WHERE startDate >= COALESCE(@StartDate, CAST(GETDATE() AS DATE)) AND
      endDate <= COALESCE(@EndDate, CAST(GETDATE() AS DATE))
1
votes

After the comment, diabolickman's question and Gordon's answer can be mixed like:

SELECT ROW_NUMBER() OVER ( ORDER BY [StartDate] ASC) as ROWNUM, [Oid] 
From [dbo].[Ument] 
Where (
    StartDate >= ISNULL(@StartDate, CAST(GETDATE() AS DATE))
    AND (@EndDate is null or EndDate <= @EndDate)
)

This way, End date is ignored if it's null, so you dont need to compare it against max date. Changed middle OR to AND, assuming user wants to filter a date range. I used ISNULL instead of COALESCE, just to show another alternative.

0
votes

I think my problem is solved! :) I change the last solution to this and the query returns what I want! 

SELECT ROW_NUMBER() OVER ( ORDER BY [StartDate] ASC) as ROWNUM, [Oid] From [dbo].[Ument] 
Where (
    StartDate >= ISNULL(@StartDate, CAST(GETDATE() AS DATE))
    and EndDate <= ISNULL(@EndDate, CAST('20501231' AS DATE))
)

Thanks for the help ramazan's and Gordon's. :)