To Get The Id's Between Two Dates

I have a table WEEK_ID WEEK_STARTDATE WEEK_YEAR WEEK_MONTH WEEK_CREATEDTS 252 10/26/2008 2008 11 2008-10-07 15:10:00.000 253 11/02/2008 2008 11 2008-10-07 15:10:00.00

Solution 1:

A simple SELECT WHERE BETWEEN should do the job for you. Pass in the two dates as parameters, and

SELECT week_id FROM TBL_S3_FISCALWEEKS_1 
WHERE WeekStartDate BETWEEN @DateParam1 AND @DateParam2

This will work even if the exact date doesn't appear in WeekStartDate

Solution 2:

So you want a query or function that returns the week_id if you feed it a date (even if it is a date that falls between your week_startdate and week_enddate), right?

You might want to try something like the following query:

 SELECT week_id
 FROM weeks
 WHERE DATEDIFF(dd, week_startdate, input_date) < 7
 AND DATEDIFF(dd, week_startdate, input_date) > 0

Check the MSDN DATEDIFF reference for more information on DATEDIFF.