Thursday, April 16, 2009

Select dates of a day between two dates.

Introduction :
Once, I wnated to list all Sundays betweeen two dates. Later I converted that code to generalize for all days the result was this function in the code sample.

To list out dates of particular day between two days is needed when we are developing any application which having weekly status functionaly. So, listing dates of particular day is very helpful in such situtations. fnGetDatesforAday Function gives dates for a day between two dates.

This function recieves three parameter DateFrom, DateTo and DayName.

• DateFrom : Date from which we want the dates of a day. It accepts date in two formats “MM/DD/YYY” and “MM-DD-YYYY”.

• DateTo : Date upto which we want the dates of a day. It accepts date in two formats “MM/DD/YYY” and “MM-DD-YYYY”.

• DayName: Day name for which we need to list dates. Valid values are “Sunday”, “Monday”, ”Tuesday”, ”Wednesday”, “Thursday”, ”Friday” and ”Saturday”.


This function uses two system function

• DATEDIFF
• DATENAME

The Logic here is first find out the number of days between two dates using DATEDIFF function which returns number of days. So using this number we can loop through each day and check for the required day in the days between start date to end date.

To get day name we use DATENAME function which gives us the day names like “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday” and “Saturday”, so compare the given day with these days If found insert the date into the table. So finally the table contains all required days dates in the table. Finally return all the dates which are inserted in the table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Shivshanker cheral
-- Create date: 18 July 2008
-- Description: To Get any weekday dates between two dates
-- =============================================
-- to list all sundays
CREATE FUNCTION fnGetDatesforAday
(
-- Add the parameters for the function here
@DtFrom DATETIME,
@DtTo DATETIME,
@DayName VARCHAR(12)
)
RETURNS @DateList TABLE ([Day] varchar(20),Dt datetime)
AS
BEGIN

IF NOT (@DayName = 'Monday' OR @DayName = 'Sunday' OR @DayName = 'Tuesday' OR @DayName = 'Wednesday' OR @DayName = 'Thursday' OR @DayName = 'Friday' OR @DayName = 'Saturday')
BEGIN
--Error Insert the error message and return
INSERT INTO @DateList
SELECT 'Invalid Day',NULL AS DAT
RETURN
END


DECLARE @TotDays INT
DECLARE @CNT INT

SET @TotDays = DATEDIFF(DD,@DTFROM,@DTTO)-- [NO OF DAYS between two dates]

SET @CNT = 0

WHILE @TotDays >= @CNT -- repeat for all days
BEGIN

-- Pick each single day and check for the day needed

IF DATENAME(DW, (@DTTO - @CNT)) = @DAYNAME
BEGIN
INSERT INTO @DateList
SELECT @DAYNAME,(@DTTO - @CNT) AS DAT
END
SET @CNT = @CNT + 1
END

RETURN
END
GO


Output:

SELECT [Day],[Dt] FROM dbo.fnGetDatesforAday('7/1/2008','8/31/2008','Sunday')

Day Dt
-------------------- -----------------------
Sunday 2008-08-31 00:00:00.000
Sunday 2008-08-24 00:00:00.000
Sunday 2008-08-17 00:00:00.000
Sunday 2008-08-10 00:00:00.000
Sunday 2008-08-03 00:00:00.000
Sunday 2008-07-27 00:00:00.000
Sunday 2008-07-20 00:00:00.000
Sunday 2008-07-13 00:00:00.000
Sunday 2008-07-06 00:00:00.000

(9 row(s) affected)
We
can list the dates in sorted order with date in different formats
according to our needs using convert function and Order by clause.

SELECT [Day],CONVERT(VARCHAR,[Dt],105) AS [Date] FROM dbo.fnGetDatesforAday('7/1/2008','8/31/2008','Sunday') ORDER BY [Dt]

Day Date
-------------------- ------------------------------
Sunday 06-07-2008
Sunday 13-07-2008
Sunday 20-07-2008
Sunday 27-07-2008
Sunday 03-08-2008
Sunday 10-08-2008
Sunday 17-08-2008
Sunday 24-08-2008
Sunday 31-08-2008

(9 row(s) affected)

For error it gives the result as Invalid Day in the Day column and Null in Date column


SELECT [Day],CONVERT(VARCHAR,[Dt],105) AS [Date] FROM dbo.fnGetDatesforAday('7/1/2008','8/31/2008','Sumday') ORDER BY [Dt]

Day Date
-------------------- ------------------------------
Invalid Day NULL

(1 row(s) affected)

Maximum Capacity Specifications for SQL Server 2005

  • Batch size : -> 65,536 * Network Packet Size
  • Bytes per short string column : -> 8000
  • Bytes per text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or XML column : -> 231 -1 bytes/p>
  • Bytes per GROUP BY, ORDER BY : -> 8060
  • Bytes per index : -> 900
  • Bytes per foreign key : -> 900
  • Bytes per primary key : -> 900
  • Bytes per row : -> 8060
  • Bytes in source text of a stored procedure : -> Lesser of batch size or 250 MB
  • Clustered indexes per table : -> 1
  • Columns in GROUP BY, ORDER BY : -> Limited only by number of bytes
  • Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement : -> 10
  • Columns per index : -> 16
  • Columns per foreign key : -> 16
  • Columns per primary key : -> 16
  • Columns per base table : -> 1024
  • Columns per SELECT statement : -> 4096
  • Columns per INSERT statement : -> 1024
  • Connections per client : -> Maximum value of configured connections
  • Database size : -> 1,048,516 terabytes
  • Databases per instance of SQL Server : -> 32767
  • Filegroups per database : -> 32767
  • Files per database : -> 32767
  • File size (data) : -> 32 terabytes
  • File size (log) : -> 32 terabytes
  • Foreign key table references per table : -> 253
  • Identifier length (in characters) : -> 128
  • Instances per computer : -> 50
  • Length of a string containing SQL statements (batch size) : -> 65,536 * Network packet size
  • Locks per connection : -> Maximum locks per server
  • Locks per instance of SQL Server : -> Up to 2,147,483,647
  • Nested stored procedure levels : -> 32
  • Nested subqueries : -> 32
  • Nested trigger levels : -> 32
  • Nonclustered indexes per table : -> 249
  • Objects concurrently open in an instance of SQL Server : -> 2,147,483,647 per database (depending on available memory)
  • Objects in a database : -> 2147483647
  • Parameters per stored procedure : -> 2100
  • Parameters per user-defined function : -> 2100
  • REFERENCES per table : -> 253
  • Rows per table : -> Limited by available storage
  • Tables per database : -> Limited by number of objects in a database
  • Tables per SELECT statement : -> 256
  • Triggers per table : -> Limited by number of objects in a database
  • UNIQUE indexes or constraints per table : -> 249 nonclustered and 1 clustered
  • Maximum Capacity Specifications for SQL Server 2005

  • Batch size : -> 65,536 * Network Packet Size
  • Bytes per short string column : -> 8000
  • Bytes per text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or XML column : -> 231 -1 bytes/p>
  • Bytes per GROUP BY, ORDER BY : -> 8060
  • Bytes per index : -> 900
  • Bytes per foreign key : -> 900
  • Bytes per primary key : -> 900
  • Bytes per row : -> 8060
  • Bytes in source text of a stored procedure : -> Lesser of batch size or 250 MB
  • Clustered indexes per table : -> 1
  • Columns in GROUP BY, ORDER BY : -> Limited only by number of bytes
  • Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement : -> 10
  • Columns per index : -> 16
  • Columns per foreign key : -> 16
  • Columns per primary key : -> 16
  • Columns per base table : -> 1024
  • Columns per SELECT statement : -> 4096
  • Columns per INSERT statement : -> 1024
  • Connections per client : -> Maximum value of configured connections
  • Database size : -> 1,048,516 terabytes
  • Databases per instance of SQL Server : -> 32767
  • Filegroups per database : -> 32767
  • Files per database : -> 32767
  • File size (data) : -> 32 terabytes
  • File size (log) : -> 32 terabytes
  • Foreign key table references per table : -> 253
  • Identifier length (in characters) : -> 128
  • Instances per computer : -> 50
  • Length of a string containing SQL statements (batch size) : -> 65,536 * Network packet size
  • Locks per connection : -> Maximum locks per server
  • Locks per instance of SQL Server : -> Up to 2,147,483,647
  • Nested stored procedure levels : -> 32
  • Nested subqueries : -> 32
  • Nested trigger levels : -> 32
  • Nonclustered indexes per table : -> 249
  • Objects concurrently open in an instance of SQL Server : -> 2,147,483,647 per database (depending on available memory)
  • Objects in a database : -> 2147483647
  • Parameters per stored procedure : -> 2100
  • Parameters per user-defined function : -> 2100
  • REFERENCES per table : -> 253
  • Rows per table : -> Limited by available storage
  • Tables per database : -> Limited by number of objects in a database
  • Tables per SELECT statement : -> 256
  • Triggers per table : -> Limited by number of objects in a database
  • UNIQUE indexes or constraints per table : -> 249 nonclustered and 1 clustered