SQL Date trick.

You ever have to write a query where you need to show a range of dates, but some of the dates are missing? Most message boards I have read want you to create some massive date table to keep track of every day and then you can use it in a join to get the missing days. Well I came up with my own solution to this, I’ve seen variations of it out there but none quite like mine. I created a function that you pass in a start date and an end date, and it returns all the dates in the middle. It also uses a SQL while loop much like you’d use a ‘FOR LOOP’ in code, which I think is pretty nifty. I also included a line to get rid of weekend dates, depending on the data you are using you may or may not want to do this. In the actual function it takes in the parameters for the dates, I wrote it this way so you can just copy and paste it into a SQL Query tool and see the magic.

declare @StartDate datetime;
declare @EndDate datetime;

DECLARE @TMP_DATES AS TABLE

(
	[Date] datetime
)

SET @StartDate = GETDATE()
SET @EndDate = @StartDate+14

 declare @i int
 SET @i = 0;
 while (@i<(DATEDIFF (d, @StartDate, @EndDate)))
 begin
 insert into @TMP_DATES values (CONVERT(CHAR(10),@StartDate+@i,101))
 SET @i=@i+1
 end

DELETE from @TMP_DATES where DATEPART(weekday,[Date]) = 1 OR DATEPART(weekday,[Date]) = 7

SELECT * from @TMP_DATES

Advertisement