Query a record between two dates, where dates might be 1900-01-01

Question

Imagine this. You need to query your pricing data table to find out what the current pricing is on a part number given a specific date. You table is formatted so that if the 'from date' is '1900-01-01 00:00:00.000' and your 'to date' is '2021-06-07 00:00:00.000', then that means any date before the 'to date' is valid.

There is also another record with 'from date' 2021-06-08 00:00:00.000 to '1900-01-01 00:00:00.000', which means anything after the date.

Here is the table visually

itemrelation	fromdate	todate	amount
A123456	1900-01-01 00:00:00.000	2021-06-07 00:00:00.000	1578.300000000000
A123456	2021-06-08 00:00:00.000	1900-01-01 00:00:00.000	1586.300000000000

Lets say the user wants to check what the price is today 2022-03-24

The query ran cannot be

SELECT itemrelation, fromdate, todate, amount
  FROM [Ax2009Live].[dbo].[PRICEDISCTABLE] 
  where itemrelation = 'A123456'
  and fromDate <= '2022-03-24'
  and toDate >= '2022-03-24'

because of the "1900" dates will prevent the query from returning any results.

Is there some type of CASE statement that would work to solve this problem? Some built in SQL command I don't know about?

Thanks for your help.

Answer 1

Thank you @HoneyBadger,

Here is the query based on your suggestion. It works great.

DECLARE @ITEMRELATION AS NVARCHAR(50)
SET @ITEMRELATION = 'A123456'

DECLARE @DATE AS DATETIME
SET @DATE = '2022-03-24'

SELECT itemrelation, fromdate, todate, amount
  FROM [Ax2009Live].[dbo].[PRICEDISCTABLE] 
  WHERE 
  itemrelation = @ITEMRELATION
  and fromDate <= @DATE
  and toDate >= @DATE
  OR 
  itemrelation = @ITEMRELATION
  and fromDate <= @DATE
  and toDate = '1900-01-01'
  OR 
  itemrelation = @ITEMRELATION
  and fromDate = '1900-01-01'
  and toDate >= @DATE