# question

## Get Days from the given start date and end date

declare @startdate datetime, @enddate datetime set @startdate='01/01/2010' set @enddate='01/31/2010' is it possible to get dates between the start date and end date without using while loop ..just by using select statement it should return all the dates between startdate and enddate... Any help ....
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Try This SELECT DATEDIFF(day,'01/01/2010','01/31/2010') ie : SELECT DATEDIFF(day,@startDate,@endDate)
0 Likes 0 ·

·
using a tally table (starting at n =1) SET dateformat MDY declare @startdate datetime,@enddate datetime set @startdate='01/01/2010' set @enddate='01/31/2010' SELECT @startdate + (n-1) FROM dbo.tally WHERE n-1 < DATEDIFF (dd, @startdate, @enddate) More on Tally tables [here][1] Or if you don't want to use a 'physical' tally table, utilise a cte-based one SET dateformat MDY declare @startdate datetime,@enddate datetime set @startdate='01/01/2010' set @enddate='01/31/2010' ;WITH Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ), Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ), Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) SELECT @startdate + (n-1) FROM (SELECT row_number() OVER(ORDER BY n) FROM Nbrs) Tally (n) WHERE n < DATEDIFF (dd, @startdate, @enddate) + 1 [1]: http://www.sqlservercentral.com/articles/T-SQL/62867/

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
no point me adding an option thats the same theory. +1 to you :)
0 Likes 0 ·
·
thank you for the almost requirement which i have got it ...but here what is happening is month and year is incrementing by 1 ...but i need days ...
0 Likes 0 ·
·
@Murali : can you post the exact code you are using
0 Likes 0 ·
·
DECLARE @N INT SET @N=1 SELECT PRDDTL_STARTDATE + (@N-1) FROM WHERE @N-1 < DATEDIFF (dd, PRDDTL_STARTDATE, PRDDTL_ENDDATE)
0 Likes 0 ·
·
@Murali : how is that code incrementing the @N variable?
0 Likes 0 ·
I wanted to add this as a comment to Kev's answer but it was too big. This is the fastest function to create a "virtual" tally table i have ever seen. You can step forward or backward and you can step more than one step at a time. (like when you want each monday in the given range) CREATE FUNCTION [DBO].[fnNumsTable]( @pStartValue BIGINT= 1, @pEndValue BIGINT= 1000000, @pIncrement BIGINT= 1 ) RETURNS TABLE AS -- +---------------------------------------------------------------------------------------------------------------- -- ! O b j e c t : [DWH].[fnNumsTable] -- ! R e t u r n s : A table with number from startvalue to end value -- ! P a r a m e t e r s : Name DataType Description -- + ======================= ============== ================================================== -- ! @pStartValue BIGINT= 1, -- ! @pEndValue BIGINT= 1000000, -- ! @pIncrement BIGINT= 1 -- + --------------------------------------------------------------------------------------------------------------- -- ! O b j e c t i v e : Return a list of all numbers between startvalue to end value -- ! The table may be used to avoid cursors, generate a list of dates etc. -- + --------------------------------------------------------------------------------------------------------------- -- ! H i s t o r y : -- + --------------------------------------------------------------------------------------------------------------- -- ! Date Who What -- + ========== ===== ======================================================================== -- ! 2009-09-21 HAWI -- +---------------------------------------------------------------------------------------------------------------- --Select n from dbo.[fnNumsTable](1000,2000,1) --Select DATEADD(D,N,GETDATE()) from dbo.[fnNumsTable](1,365,7) RETURN( WITH BaseNum ( N ) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), L1 ( N ) AS ( SELECT bn1.N FROM BaseNum bn1 CROSS JOIN BaseNum bn2 ), L2 ( N ) AS ( SELECT a1.N FROM L1 a1 CROSS JOIN L1 a2 ), L3 ( N ) AS ( SELECT TOP ((ABS(CASE WHEN @pStartValue < @pEndValue THEN @pEndValue ELSE @pStartValue END - CASE WHEN @pStartValue < @pEndValue THEN @pStartValue ELSE @pEndValue END))/ABS(@pIncrement)+ 1) a1.N FROM L2 a1 CROSS JOIN L2 a2 ), Tally ( N ) AS ( SELECT row_number() OVER (ORDER BY a1.N) FROM L3 a1 ) SELECT ((N - 1) * @pIncrement) + @pStartValue AS N FROM Tally ); GO

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Wow, very fast and very usable. Thanks for sharing this one Hakan. :) +1
0 Likes 0 ·
·
The best with this function is that it doesn't consume any IO, only CPU and memory, and it is an inline tabled valued function that doesn't suffer from the drawbacks of scalar valued functions.
0 Likes 0 ·

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.