How to select records with in Date Range

Selecting records within a given date range is one of the common requirement these days, but many people find it difficult. The most common mistake people make is always try to do the comparison as the same as the way we do for numbers as shown below

Wrong comparison
SELECT *
FROM Table
WHERE StartDate >= @P_StartDate
AND EndDate =< @P_EndDate

This wills not retrieve the qualified records as the comparison will not be against the range instead it will be against two dates and it will ignore any records fall within the range. The trick is to change the parameter to check the date in the reverse order as shown below.

Modified Query
SELECT *
FROM Table
WHERE EndDate > = @P_StartDate
AND StartDate =< @P_EndDate

How to take week or month wise report in SQL Server

I was breaking my head whole day for writing a stored procedure which produces week wise report for plotting a revenue trend. I came across this solution which is fairly a simple one. Just thought of sharing this

The below code is written using the SQL Server built in functions DATEDIFF & DATEADD to produce the report

Select DATEADD(WK, datediff(WK, 0, CollectionDate),0) as week,
SUM(Amount)
from Revenue
group by dateadd(WK, datediff(WK 0, CollectionDate),0)
order by 1 asc

The logic here is to find out the corresponding week from Default date (01-01-1900) and adding the default date again to the result will get the corresponding week in date format. The same can be used for generating month wise report as well by just replacing "wk" with "MM"

Select DATEADD(MM, datediff(MM, 0, CollectionDate),0) as week,
SUM(Amount)
from Revenue
group by dateadd(MM, datediff(MM 0, CollectionDate),0)
order by 1 asc

Note: adding “0” will be automatically converted to default date “01-01-1900”