x

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 ....
more ▼

asked Jul 19, 2010 at 02:07 AM in Default

Murali gravatar image

Murali
906 103 117 120

Try This
SELECT DATEDIFF(day,'01/01/2010','01/31/2010')
ie : SELECT DATEDIFF(day,@startDate,@endDate)
Jul 19, 2010 at 02:28 AM Alban Lijo
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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/
more ▼

answered Jul 19, 2010 at 02:50 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

no point me adding an option thats the same theory. +1 to you :)
Jul 19, 2010 at 02:54 AM Fatherjack ♦♦
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 ...
Jul 19, 2010 at 03:12 AM Murali
@Murali : can you post the exact code you are using
Jul 19, 2010 at 03:22 AM Kev Riley ♦♦
DECLARE @N INT SET @N=1 SELECT PRDDTL_STARTDATE + (@N-1) FROM WHERE @N-1 < DATEDIFF (dd, PRDDTL_STARTDATE, PRDDTL_ENDDATE)
Jul 19, 2010 at 03:29 AM Murali
@Murali : how is that code incrementing the @N variable?
Jul 19, 2010 at 03:37 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jul 19, 2010 at 04:26 AM

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

Wow, very fast and very usable. Thanks for sharing this one Hakan. :) +1
Jul 19, 2010 at 05:34 AM Fatherjack ♦♦
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.
Jul 19, 2010 at 11:20 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x24

asked: Jul 19, 2010 at 02:07 AM

Seen: 3324 times

Last Updated: Dec 18, 2012 at 07:01 AM