EF VAT table Calculating Vat rate on specific date

I am developing an MVC3 application in C# using Entity Framework. I have a VAT rates table which has VAT rate, Start Date, End date fields. Start Date and end date can be null to indicate that either this is a historical rate or an ongoing rate. What I am trying to figure out, is how do create a single query that takes these nulls 开发者_如何学Cinto account to give me the vat rate when I pass in a single date. (nb example shows uk date format dd/mm/yyyy

+--------------+-------------+----------+
| Start Date   | End Date    | Vat rate |
+--------------+-------------+----------+
| Null         | 01/01/2011  | 19       |
+--------------+-------------+----------+
| 02/01/2011   | 08/08/2011  | 20       |
+--------------+-------------+----------+
| 09/08/2011   | Null        | 21       |
+--------------+-------------+----------+

Thanks for your time

If you are using EF, you can do something like this:

DateTime first = DateTime.Parse("01-01-1900");
DateTime last = DateTime.Parse("01-01-2100");
var rate = db.VatRates.Where(
    vr => input > (vr.StartDate ?? first) && input < (vr.EndDate ?? last)).First().Rate;

According to comment below, without EndDate column try this:

DateTime start = DateTime.Parse("01-01-1900");
var rate = db.VatRates.Where(vr => input > (vr.StartDate ?? start))
    .OrderByDescending(vr => vr.StartDate).First().Rate;

Works for me :)

Assuming you pass in @Date

SELECT      VATRate 
FROM        VATRateTable
WHERE       @Date
BETWEEN     ISNULL(StartDate, '1900-01-01') AND ISNULL(EndDate, '2100-01-01')