Between Months query help please

Question

Hi all, could somebody please aid me in my query please. I use linq2sql and am using a datatable adapter to fill a reportviewer. I am only interested in the Month and year from a datetime picker. I can get the single month to work but how do I do between or from month to month ?

See my query for single month 

SELECT Con1, Con10, Con2, Con3, Con4, Con5, Con6, Con7, Con8, Con9, Docket_Category, Docket_Date, Docket_DateRaised, Docket_EngFinish, Docket_EngStart, Docket_EngineerName, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_SubCategory, Duration, Module, Monitor_Time, Operator_Name, Section, Waittime, spare8 FROM DocketTB WHERE (DATEPART(yyyy, @date1) = DATEPART(yyyy, Docket_Date)) AND (DATEPART(mm, @date1) = DATEPART(mm, Docket_Date))

How could I adapt this to say make it all dates in Jan to Feb using @date1 variable and @date2 vairable

Thanks

Answer 1

Although I do not use ling2sql, so I have not much to say about the datetime picker, but once you have the months selected it is not too difficult.

The thing is that time is a continuum.  So the very best way to query for both accuracy and performance is to use the pattern:   WHERE Docket_Date >= to @Date1 and Docket_Date < @Date2

So here is a sample:

DECLARE @Date1 DATETIME
DECLARE @Date2 DATETIME
-- Purposefully made the dates inaccurate for illustration purposes
SET @Date1 = '2012-01-05'
SET @Date2 = '2012-02-28'
-- Make sure date starts at beginning of the month
SELECT @Date1 = DATEADD(DAY,(DATEPART(DAY,@Date1) * -1) + 1, @Date1)
-- Calculate first day of following month for @Date2
SELECT @Date2 = DATEADD(MONTH,1,
       (DATEADD (DAY,(DATEPART(DAY,@Date2) * -1) + 1, @Date2)))
SELECT @Date1, @Date2
SELECT Con1, Con10, Con2, Con3, Con4, Con5, Con6, 
    Con7, Con8, Con9, Docket_Category, Docket_Date, 
    Docket_DateRaised, Docket_EngFinish, Docket_EngStart, 
    Docket_EngineerName, Docket_Id, Docket_Machine, 
    Docket_Number, Docket_Status, Docket_SubCategory, 
    Duration, Module, Monitor_Time, Operator_Name, 
    Section, Waittime, spare8 
FROM DocketTB 
WHERE Docket_Date >= @Date1 AND Docket_Date < @Date2 

This pattern also allows you to use an index on Docket_Date (if you have one) to improve the performance of the query.

RLF