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 ....
Try This SELECT DATEDIFF(day,'01/01/2010','01/31/2010') ie : SELECT DATEDIFF(day,@startDate,@endDate)
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/
no point me adding an option thats the same theory. +1 to you :)
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 ...
DECLARE @N INT
SET @N=1
SELECT PRDDTL_STARTDATE + (@N-1)
FROM
WHERE
@N-1 < DATEDIFF (dd, PRDDTL_STARTDATE, PRDDTL_ENDDATE)
@Murali : how is that code incrementing the @N variable?
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
Wow, very fast and very usable. Thanks for sharing this one Hakan. :) +1
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.
