February 8, 2006 at 2:24 am
Hello out there!
If I have 2 dates say
10/5/2005 and 1/31/2006 is there a function that can be used to display the dates inbetween?
If there is no function, can anyone help me with the best way that I can display the dates in between
Any help will be highly appreciated
February 8, 2006 at 3:24 am
DECLARE @LOWERDATE DATETIME
DECLARE @UPPERDATE DATETIME
SET @LOWERDATE = '2006-01-01'
SET @UPPERDATE = '2006-01-04'
WHILE @LOWERDATE < @UPPERDATE
BEGIN
PRINT @LOWERDATE
SET @LOWERDATE = @LOWERDATE + 1
END
You can play around with the output to change into the date type you want. You can also convert this into a stored procedure or function if you need to reuse the code.
February 9, 2006 at 2:39 am
Hi,
you can write a UDF that returns a table variable containing all the dates between start and end date inclusive...
CREATE FUNCTION udf_DateRange (@dtStartDate DATETIME,
@dtEndDate DATETIME)
RETURNS @DateRange TABLE
(InDate DATETIME)
AS
BEGIN
WHILE @dtStartDate <= @dtEndDate
BEGIN
INSERT INTO @DateRange (InDate) VALUES (@dtStartDate)
SET @dtStartDate=@dtStartDate+1
END
RETURN
END
You can use it in place of a table in query...
SELECT InDate FROM udf_DateRange(startdate, enddate)
eg: (this shows all dates irrespective of whether there were sales)
SELECT DR.InDate, SUM(GI.Fee) 'Sales'
FROM
udf_DateRange('01-Jan-2006','01-Feb-2006') DR
LEFT JOIN GIMilestones GI
on (GI.Milestone=DR.InDate)
GROUP BY DR.InDate
David
If it ain't broke, don't fix it...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply