Thursday, 22 December 2011

Trick- Date Range

To display continues date between 2 date ranges uses this trick

SET DATEFORMAT DMY

GO

IF NOT EXISTS(SELECT * FROM sysobjects WHERE xtype='U' and name like 'Numbers')

      BEGIN

            SELECT TOP 10000 IDENTITY(int,1,1) AS Number

                     INTO Numbers

          FROM sys.columns s1

                   CROSS JOIN sys.columns s2

                   

          ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

      END

GO   

 

DECLARE @Start DATETIME

DECLARE @End DATETIME

 

SET @Start='01-01-2011'

SET @End='28-02-2011'

SELECT

    @Start+Number-1

    FROM Numbers

    WHERE Number<=DATEDIFF(day,@Start,@End)+1

GO

 

Posted by: MR. JOYDEEP DAS

 

2 comments: