Bimal Das Bimal Das - 6 months ago 7
SQL Question

Get database records which satisfy two date ranges and within a specific timing in SQL server

I have following [RestaurantOffer] table.
Offers, which is valid only within two date ranges (i.e FromDate and ToDate) and at a particular timing(FromTime,ToTime).

enter image description here

So I want to write a query which would give me all Offers on current date(Today) and the time will be more than current Time(Today's Current Time). Because I don't want to get the expired Offers.

Database :

FromDate(date),
ToDate(date),
FromTime(time),
ToTime(time)


UPDATE :
**Note :**This Offers is not valid all 24 hours.. It is valid only FromTime-ToTime Range.
What I want :

1st: Give me All today's records if today satisfies FromDate and Todate
range.

2nd : After getting all the records for today , I want to get all the records which if more than or equal today's Current Time.

Answer

Try this:

--DROP TABLE #temp

CREATE TABLE #temp
    (
      FROMDate DATE
    , ToDate DATE
    , FromTime TIME
    , ToTime TIME
    );

INSERT  INTO #temp
        ( FROMDate, ToDate, FromTime, ToTime )
VALUES  ( '2016-05-01'  -- FROMDate - date
          , '2016-06-01'  -- ToDate - date
          , '11:30:00:000'  -- FromTime - time
          , '17:30:00:000'  -- ToTime - time
          ),
        ( '2016-05-01'  -- FROMDate - date
          , '2016-06-01'  -- ToDate - date
          , '11:30:00:000'  -- FromTime - time
          , '17:30:00:000'  -- ToTime - time
          ),
        ( '2016-05-01'  -- FROMDate - date
          , '2016-06-01'  -- ToDate - date
          , '11:30:00:000'  -- FromTime - time
          , '17:30:00:000'  -- ToTime - time
          ),
        ( '2016-05-01'  -- FROMDate - date
          , '2016-06-01'  -- ToDate - date
          , '11:30:00:000'  -- FromTime - time
          , '19:30:00:000'  -- ToTime - time
          );

SELECT  FROMDate
      , ToDate
      , FromTime
      , ToTime
FROM    #temp
WHERE   ( CAST(GETDATE() AS DATE) >= FROMDate
          AND CAST(GETDATE() AS DATE) <= ToDate
        )
        AND ( CAST(GETDATE() AS TIME) >= FromTime
              AND CAST(GETDATE() AS TIME) <= ToTime
            );