I have been working with SQL Server for too many years to count and this is baffling; perhaps someone has a clue on the cause. I am running this on SQL Server 2022. I put together a simple function to return a primary key integer for a lookup table that has mileage rates that are active within a date time span for a vehicle type. When I execute that function it is returning inconsistent results and I have narrowed it down to the date time span. Below are the table and function with the final question:
Table (Note: The ValidFrom and ValidUntil are used to create the time span for changing mileage effective rates. The most current mileage rates for each vehicle type has a NULL datetime in the ValidUntil column.)
CREATE TABLE [dbo].[MileageRate](
[RateId] [INT] IDENTITY(1,1) NOT NULL,
[VehicleType] [NVARCHAR](50) NOT NULL,
[Abbreviation] [NVARCHAR](2) NOT NULL,
[ValidFrom] [DATETIME2](7) NOT NULL,
[ValidUntil] [DATETIME2](7) NULL
CONSTRAINT [PK_MileageRate_RateId] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Function providing inconsistent results where sometime it was coming back as RateId = zero and sometimes the RateId was the correct value.
CREATE FUNCTION [dbo].[fn_GetRateId](
--Testing code executions
SELECT dbo.fn_GetRateId('motor pool');
DECLARE @rateId INTEGER = 0;
--Set comparisonDate as consistent value
DECLARE @compareDate DATETIME = GETDATE();
SET @vehicleType = LOWER(TRIM(ISNULL(@vehicleType, '')));
--Look for abbreviation first
IF LEN(@vehicleType) = 2
SET @rateId = ISNULL((SELECT TOP(1) RateId FROM [dbo].[MileageRate] WHERE LOWER(Abbreviation) = @vehicleType AND @compareDate BETWEEN ValidFrom AND ISNULL(ValidUntil, @compareDate) ORDER BY ValidFrom), 0);
--If not found, look by VehicleType
IF @rateId = 0
SET @rateId = ISNULL((SELECT TOP(1) RateId FROM [dbo].[MileageRate] WHERE LOWER(VehicleType) = @vehicleType AND @compareDate BETWEEN ValidFrom AND ISNULL(ValidUntil, @compareDate) ORDER BY ValidFrom), 0);
I resolved the function to provide a consistent result if I changed the date search to:
DATEADD(SECOND, -1, @compareDate) BETWEEN ValidFrom AND ISNULL(ValidUntil, @compareDate)
Does anyone understand why I had to do this? I have always understood the date BETWEEN predicate to be inclusive of the start and end dates in the result.
Thank you in advance.
- This topic was modified 1 month, 1 week ago by Ed Thompson.