question

Murali avatar image
Murali asked

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 ....
t-sqlloop
1 comment
10 |1200

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

Alban Lijo avatar image Alban Lijo commented ·
Try This SELECT DATEDIFF(day,'01/01/2010','01/31/2010') ie : SELECT DATEDIFF(day,@startDate,@endDate)
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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/
8 comments
10 |1200

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

Fatherjack avatar image Fatherjack ♦♦ commented ·
no point me adding an option thats the same theory. +1 to you :)
0 Likes 0 ·
Murali avatar image Murali commented ·
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 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@Murali : can you post the exact code you are using
0 Likes 0 ·
Murali avatar image Murali commented ·
DECLARE @N INT SET @N=1 SELECT PRDDTL_STARTDATE + (@N-1) FROM WHERE @N-1 < DATEDIFF (dd, PRDDTL_STARTDATE, PRDDTL_ENDDATE)
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@Murali : how is that code incrementing the @N variable?
0 Likes 0 ·
Show more comments
Håkan Winther avatar image
Håkan Winther answered
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
2 comments
10 |1200

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

Fatherjack avatar image Fatherjack ♦♦ commented ·
Wow, very fast and very usable. Thanks for sharing this one Hakan. :) +1
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
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 ·
sandeepmittal11 avatar image
sandeepmittal11 answered
10 |1200

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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