I have a calendar table that shows all dates from 01/01/2015 to 12/31/2016, essentially the calendar shows the date and isworkday. I need to be able to pass a begindate and enddate to a stored procedure and insert all the dates that are in the range in a table. So for example something like this

Create Procedure [dbo].[DateRangeTest]
  @begindate date
  ,@enddate date

Set @begindate = '01/01/2016'
Set @enddate = '01/31/2016'

Now here is where my lack of knowledge comes to play, how can I then take all the dates in that range that are flagged as workdays from my workcalendar table and insert those specific dates into a temp table?


Guessing at column names:

INSERT #temp(dateColumn) 
  SELECT dateColumn
  FROM dbo.WorkCalendar
  WHERE IsWorkDay = 1
  AND dateColumn >= @begindate
  AND dateColumn < DATEADD(DAY, 1, @enddate);

Also please try to avoid regional formats like m/d/y. These will bite you in the end, because someone will run your code on a server with different language or regional settings, and it will be interpreted as d/m/y. See Bad habits to kick : mis-handling date / range queries. Also, for information on why I didn't use BETWEEN, see What do BETWEEN and the devil have in common? It may work for the date data type, but I'd rather be consistent than surprised.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.