select date between 2 datetime fields based on current Date

I have a table that has the following values (reduced for brevity)

+--------+---------------------+---------------------+----------------+------------------+
| Period | Periodfrom          | Periodto            | Glperiodoracle | Glperiodcalendar |
+========+=====================+=====================+================+==================+
| 88     | 2022-01-01 00:00:00 | 2022-01-28 00:00:00 | JAN-FY2022     | JAN-2022         |
+--------+---------------------+---------------------+----------------+------------------+
| 89     | 2022-01-29 00:00:00 | 2022-02-25 00:00:00 | FEB-FY2022     | FEB-2022         |
+--------+---------------------+---------------------+----------------+------------------+
| 90     | 2022-02-26 00:00:00 | 2022-04-01 00:00:00 | MAR-FY2022     | MAR-2022         |
+--------+---------------------+---------------------+----------------+------------------+
| 91     | 2022-04-02 00:00:00 | 2022-04-29 00:00:00 | APR-FY2022     | APR-2022         |
+--------+---------------------+---------------------+----------------+------------------+
| 92     | 2022-04-30 00:00:00 | 2022-05-27 00:00:00 | MAY-FY2022     | MAY-2022         |
+--------+---------------------+---------------------+----------------+------------------+
| 93     | 2022-05-28 00:00:00 | 2022-07-01 00:00:00 | JUN-FY2022     | JUN-2022         |
+--------+---------------------+---------------------+----------------+------------------+
| 94     | 2022-07-02 00:00:00 | 2022-07-29 00:00:00 | JUL-FY2022     | JUL-2022         |
+--------+---------------------+---------------------+----------------+------------------+
| 95     | 2022-07-30 00:00:00 | 2022-08-26 00:00:00 | AUG-FY2022     | AUG-2022         |
+--------+---------------------+---------------------+----------------+------------------+
| 96     | 2022-08-27 00:00:00 | 2022-09-30 00:00:00 | SEP-FY2022     | SEP-2022         |
+--------+---------------------+---------------------+----------------+------------------+
| 97     | 2022-10-01 00:00:00 | 2022-10-28 00:00:00 | OCT-FY2023     | OCT-2022         |
+--------+---------------------+---------------------+----------------+------------------+

I want to make a stored procedure that when executed (without receiving parameters) will return the single row corresponding to the date between PeriodFrom and PeriodTo based on execution date.

I have something like this:

Select top 1 Period, 
       Periodfrom, 
       Periodto, 
       Glperiodoracle, 
       Glperiodcalendar
From Calendar_Period
Where Periodfrom <= getdate()
      And Periodto >= getdate() 

I understand that using BETWEEN could lead to errors, but would this work in the edge cases taking in account seconds, right?

>Solution :

Looks like (i) your end date is inclusive (ii) the time portion is always 00:00. So the correct and most performant query would be:

where cast(getdate() as date) between Periodfrom and Periodto

It will, for example, return the first row when the current time is 2022-01-28 23:59:59.999.

Leave a Reply