CODEWITHSUNDEEP
sqlsql-serverdate
Dan Walters
Dan Walters

Reputation: 1376

Check if input date falls within table of start and end dates

I'd like to run a query to check if an input date falls within any given term dates from a table.

Example table of Term Dates:

<table border="1">
<tr><th>termID</th><th>txtStartDate</th><th>txtFinishDate</th></tr>
<tr><td>37</td><td>2017-09-05 00:00:00</td><td>2017-12-15 23:59:00</td></tr>
<tr><td>38</td><td>2018-01-09 00:00:00</td><td>2018-03-29 23:59:00</td></tr>
<tr><td>39</td><td>2018-04-24 00:00:00</td><td>2018-07-06 23:59:00</td></tr>
<tr><td>40</td><td>2018-09-04 00:00:00</td><td>2018-12-14 23:59:00</td></tr>
<tr><td>41</td><td>2019-01-08 00:00:00</td><td>2019-03-29 23:59:00</td></tr>
<tr><td>42</td><td>2019-04-24 00:00:00</td><td>2019-07-05 23:59:00</td></tr></table>

Given an date, let's say today's date, does it fall between any of the rows' start and end date.

Example code approach

date = now()
for (row in rows):
    if date between row.txtStartDate and row.txtEndDate:
        return "yes"

I can use between logic with given dates but unsure how to apply this to the entire table.

select case when getdate() between '2019-04-24 00:00:00' and '2019-07-05 23:59:00' then 'yes' else 'no' END

Thanks

Upvotes: 3

Views: 172

Answers (3)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try with IF EXISTS also as shown below.

If Exists (Select 1 from YourTable where GETDATE() between txtStartDate AND txtEndDate)
Begin
    --Your if logic
end
else
begin
    --Your else logic
end

Upvotes: 0

EzLo
EzLo

Reputation: 14189

You need to reference the table's columns instead of hard-coding the values. You do this using the table's alias and the proper column name.

DECLARE @InputDate DATE = '2019-01-05'

SELECT
    T.*
FROM
    YourTable AS T
WHERE
    @InputDate BETWEEN T.txtStartDate AND T.txtEndDate

Using a WHERE clause will filter the rows from the table and only display the ones in which the condition is true. If you move your condition as an expression of a new column (in the SELECT column list) then it will display all rows with this new expression:

DECLARE @InputDate DATE = '2019-01-05'

SELECT
    T.*,
    IsInputDateBetweenDates = CASE
        WHEN @InputDate BETWEEN T.StartDate AND T.EndDate THEN 'Yes'
        ELSE 'No' END
FROM
    YourTable AS T

Upvotes: 1

StepUp
StepUp

Reputation: 38094

Try to use WHERE operator:

SELECT *
FROM YourTable yt
WHERE GETDATE() BETWEEN yt.txtStartDate AND yt.txtEndDate

Upvotes: 1

Related Questions