CODEWITHSUNDEEP
sql-serverdatestored-procedures
Nuno Ribeiro
Nuno Ribeiro

Reputation: 2555

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

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

Upvotes: 0

Views: 2400

Answers (3)

Nuno Ribeiro
Nuno Ribeiro

Reputation: 2555

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. :)

Upvotes: 0

Ramazan Binarbasi
Ramazan Binarbasi

Reputation: 787

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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))

Upvotes: 4

Related Questions