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

avatar image

Murali
906 109 119 123

 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

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


more ▼

answered Jul 19, 2010 at 02:50 AM

avatar image

Kev Riley ♦♦
64k 48 61 81

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

avatar image

Håkan Winther
16.5k 36 45 57

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.

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:

x1067
x28

asked: Jul 19, 2010 at 02:07 AM

Seen: 4249 times

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

Copyright 2016 Redgate Software. Privacy Policy