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?
CodePudding user response：
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.