Find dates inside range when date is stored as year, month and day separately

Question

I have a table which has columns Year, Month, Day, which are INT, and have some other columns. The size of the table is very huge.

Now, I want to retrieve the data within the date range, say between @StartDate and @EndDate.

The query I'm using is:

SELECT * FROM DataTable
WHERE 10000*M.[YEAR]+100*M.[Month]+M.[Day] >= 10000*YEAR(@StartDate)+100*MONTH(@StartDate)+DAY(@StartDate) 
    AND 10000*M.[YEAR]+100*M.[Month]+M.[Day] < 10000*YEAR(@EndDate)+100*MONTH(@EndDate)+DAY(@EndDate)

This makes the SQL Server scan the index, not seek index.

Is there a way to make it as Index Seek? I mean how to change the logic here?

I'm using SQL Server, but I guess it applies to other database too.

Answer 1

You might be interested in using DATEFROMPARTS ( year, month, day )

SELECT * FROM DataTable M
WHERE DATEFROMPARTS(M.[Year], M.[Month], M.[Day]) between @StartDate and @EndDate

Edit Based on the comment, I recommend creating a date persisted calculated field that will combine the Year and Month and Day

Hope this will help you

Answer 2

Maybe something like this would do it:

DECLARE @Start date = '2016-01-23'
DECLARE @End date = '2017-04-22';



SELECT * FROM DataTable DT
JOIN (
    SELECT YEAR(@Start) SY
        , MONTH(@Start) SM
        , DAY(@Start) SD
        , YEAR(@End) EY
        , MONTH(@End) EM
        , DAY(@End) ED
        ) MMM
    ON ( 
        MMM.SY < DT.YEAR
        OR ( MMM.SY = DT.YEAR AND 
             ( MMM.SM < DT.MONTH OR
                ( MMM.SM = DT.MONTH AND MMM.SD <= DT.DAY )
             )
            )
        )
        AND 
        ( 
        MMM.EY > DT.YEAR
        OR ( MMM.EY = DT.YEAR AND 
             ( MMM.EM > DT.MONTH OR
                ( MMM.EM = DT.MONTH AND MMM.ED >= DT.DAY )
             )
            )
        )

    ;

Answer 3

Can we try in this way:

select *
from dateCompare
where concat(Eyear, Emonth, Eday) between replace(@startDate, '-', '') 
                                      and replace(@endDate, '-', '')

Answer 4

Can you tell me how do i populate my table with dummy data ?

Don't use *,instead mention all require column along with their data type so that it give fair idea whent creating index

If you answer my above question then I update my answer accordingly.

Try this,

    DECLARE @Start date = '2000-01-23'
    DECLARE @End date = '2017-04-22';

   CREATE table MainTable(yr int not null,mnth int not null,d int not null)
create nonclustered index #MainTable_ymd on MainTable(yr ,mnth ,d )
----create nonclustered index #MainTable_y on #MainTable(yr)include(mnth ,d ) 
----here include(other column) so that it is covering index
----truncate table MainTable

insert into MainTable WITH(tablock)
select year(dt),month(dt),day(dt)
from
(
select top (10000000) dateadd(day,ROW_NUMBER()over(order by a.number)/5,'1900-01-01')dt from master..spt_values a 
,master..spt_values  b
)t4

I populate maintable with dummy data with 63,25,225 rows

   --Solution 1

SELECT *
FROM (
    SELECT yr
        ,mnth
        ,d
        ,cast(cast(yr AS VARCHAR(4)) + right('00' + cast(mnth AS VARCHAR), 2) + right('00' + cast(d AS VARCHAR), 2) AS DATE) dt
    FROM MainTable
    WHERE yr >= year(@Start)
        AND yr <= year(@End)
    ) t4
WHERE dt >= @Start
    AND dt <= @End


--RETURN
-- Solution 2
-- here put the result in temp table

CREATE table #tmp1(yr int,mnth int,d int,dt date not null)

INSERT INTO #tmp1
SELECT yr
    ,mnth
    ,d
    ,cast(cast(yr AS VARCHAR(4)) + right('00' + cast(mnth AS VARCHAR), 2) + right('00' + cast(d AS VARCHAR), 2) AS DATE) dt
FROM MainTable
WHERE yr >= year(@Start)
    AND yr <= year(@End)


create clustered index #tmp1dt on #tmp1(dt)

select * from #tmp1
where dt>=@Start and dt<=@End

--drop table MainTable

drop table #tmp1

I am getting Index seek in both solution.

With JOhnRC query I get table scan and parallelism ,with urlreader also i get table scan using same sample data.

@Monah query-- TableScan

Answer 5

You can try this.

select * from DataTable M
where 
(   M.[YEAR] > YEAR(@StartDate)
    OR (M.[YEAR] = YEAR(@StartDate)
        AND M.[Month] > Month(@StartDate) )
    OR (M.[YEAR] = YEAR(@StartDate)
        AND M.[Month] = Month(@StartDate)
        AND M.[Day]  >= Day(@StartDate) )
)
AND 
(   M.[YEAR] < YEAR(@EndDate)
    OR (M.[YEAR] = YEAR(@EndDate)
        AND M.[Month] < Month(@EndDate) )
    OR (M.[YEAR] = YEAR(@StartDate)
        AND M.[Month] = Month(@EndDate)
        AND M.[Day]  < Day(@EndDate)    )
)

Answer 6

Here is another answer which definitely uses index seek on DataTable (assuming the index is (YEAR, MONTH, DAY) when using SQL SERVER 2014. However, I suspect this might take longer than the simpler approach unless the selected date range is quite narrow. I would be interested to know how it performs on the real data.

The approach is to create a table of all possible dates between the start and end date, then join this table to the DataTable on exact match of YEAR, MONTH and DAY.

-- Paramters setting date range
DECLARE @Start date = '2016-01-23'
DECLARE @End date = '2017-04-22';

-- Create a table of all dates between start and end dates
CREATE TABLE #DATES 
(
    YY int
    , MM int
    , DD int
);

-- There are better ways to do this but for the sake of simplicity...
WHILE @Start <= @End
begin
 INSERT INTO #DATES VALUES ( YEAR(@Start), MONTH(@Start), DAY(@Start) );
 SET @Start = Dateadd(Day,1, @Start);
end ;

-- Finally, join the DataTable to the created dates with exact match
SELECT DT.* 
    FROM DataTable DT
        JOIN #DATES DD
        ON DD.YY = DT.YEAR
        AND DD.MM = DT.MONTH
        AND DD.DD = DT.DAY;

-- Finished with the dates table
DROP TABLE #DATES;