Best practice: Searching table against day, month or year..

Question

I've got a table with a "date" column, where a user input will be queried against (using stored procedure)..and results will be shown on a datagrid..

now a user can either enter a year, Year/month , Year/month/day.. (from drop down lists)

i know there r many possible ways to handle the different queries.. however i am trying to figure out which would be best practice:

Solution 1: having 3 different stored procedures , one for every case.

Solution 2: having 1 stored procedure, with 1 extra parameter as searchlvl , then using IF ELSE statements to decide what lvl of search should be applied.

Solution 3: having 1 stored procedure, and sending the datetime as 3 different parameters , then checking IF parameter is null , and using that to decide search lvl

Solution 4: your suggestions :)

NOTE: i know how to do partial search(using datepart), my question is about best practice among the 3 solutions i offered or any other solution offered in the answers.. Like which would be faster, lighter on database and such.. and which would be slower, heavier..

Answer 1

You can use datepart to get the parts of you date you want to filter against as

declare @table table(
     DateVal DATETIME
)

INSERT INTO @table SELECT GETDATE()


DECLARE @Year INT,
     @Month INT,
     @Day INT

SELECT  @Year = 2009

SELECT  DATEPART(YY, DateVal) DateYear,
     DATEPART(MM, DateVal) DateMonth,
     DATEPART(DD, DateVal) DateDay,
     *
FROM @table
WHERE   (DATEPART(YY, DateVal) = @Year OR @Year IS NULL)
AND  (DATEPART(MM, DateVal) = @Month OR @Month IS NULL)
AND  (DATEPART(DD, DateVal) = @Day OR @Day IS NULL)

Answer 2

There are no levels.

When user selects year 2009, you search rows where date >= '2009.01.01 00:00' and < '2010.01.01 00:00'.

When he selects month 01 of year 2009 you search where date >= '2009.01.01 00:00' and < '2009.02.01 00:00'.

Of course you don't pass dates as strings, you should use CONVERT() or pass dates as DATETIME type. This is universal solution and will be fast, because it will use indexes. You can create stored procedure that takes two dates, it will allow to search by every date range, not only year/month/day.

Answer 3

I'd do none of the above.

You should design you stored procedure to take three different ints, one for day, one for month and one for year. Leave the parameters nullable, but establish a convention so only meaningful parameter combinations are used. Then you construct a MINDATE and MAXDATE from the parameters.

Searching Datetime columns based on day/year/month requires a query like:

SELECT * FROM table WHERE date > MINDATE AND date < MAXDATE

which is pretty inefficient but not a definite problem.

Another approach (if the table is huge) would be to create an indexed view with year/month/day integer columns and search for exact matches there. To create such a view use DATEPART().

Answer 4

I'd pass in year/month/date as separate parameters into one stored proc, say default of NULL.

Then, I'd use DATEADD to build up from/to datetimes and use that

...
SELECT
    @ToYear = ISNULL(@ToYear, DATEPART(year, GETDATE()), --or some base value, such as "1900"
    @ToMonth = ...

...
SELECT
    @DateTo = DATEADD(year, @ToYear, DATEADD(month, @ToMonth, DATEADD(day, @ToDay, 0), 0), 0)
....
SELECT * FROM myTable WHERE DateColumn >= @DateFrom AND DateColumn <= @DateTo

I would not use any functions on columns or conditional logic to switch between selects