FInd Sundays only

rakeshs312

Hi all, I have two dates, from date and to date. i need to display only sundays between that days[IN SQLSERVER2008] How can i do this. Please help and thanks in advance Rakesh

Wendelius

One way to do this is to use a function which generates the dates between the range and then in where clause check if sunday is in date range using DATEPART function with weekday option. One example of generating the dates is mentioned in Using Table-Valued Functions in SQL Server[^], function DatesBetween. If yuo choose to use such approach the query could be something like:

SELECT ...
FROM DatesBetween(startdate, enddate) a
WHERE DATEPART(weekday, a.DateValue) = 1;

Note that the datepart result may be different in your environment depending on the SET DATEFIRST option.

The need to optimize rises from a bad design.My articles[^]

J4amieC

On SQL 2008 you can use a Common Table Expression to generate a recursive list. You can then use the DATEPART function to select only sundays from this list:

DECLARE @start DATETIME = '2011-01-01'
DECLARE @end DATETIME = '2011-03-31'

;WITH dateRange (date)
AS
(
SELECT
@start Date
UNION ALL
SELECT
DATEADD(day, 1, Date) Date
FROM
dateRange
WHERE
Date < @end
)
SELECT * FROM dateRange
WHERE DATEPART(WEEKDAY,date) = 1 -- 1 is Sunday

J4amieC

The linked example is slow and horrible. Multiple inserts into a table is no longer required on SQL 2008. Use CTE's.

Wendelius

CTE is one possibility. Curious to know what makes the example horrible? Note that the insert in the example isn't using a persistent table but a table-type which is quite different.

The need to optimize rises from a bad design.My articles[^]

J4amieC

Quick and dirty trial. Not particularly scientific but gives you some idea. On my local instance of SQL Server, this script (note the long time frame to bump the execution time to something measurable):

DECLARE @start DATETIME = '1950-01-01'
DECLARE @end DATETIME = '2011-03-31'

;WITH dateRange (date)
AS
(
SELECT
@start Date
UNION ALL
SELECT
DATEADD(day, 1, Date) Date
FROM
dateRange
WHERE
Date < @end
)
SELECT * FROM dateRange WHERE DATEPART(WEEKDAY,date) = 1
OPTION (MAXRECURSION 32767);

returns immediately (<1sec) Whereas this script:

DECLARE @start DATETIME = '1950-01-01'
DECLARE @end DATETIME = '2011-03-31'
DECLARE @dates TABLE (date DATETIME)

WHILE (@start <= @end)
BEGIN
INSERT INTO @dates VALUES (@start);
SET @start = DATEADD(day, 1, @start);
END;
SELECT * FROM @dates WHERE DATEPART(WEEKDAY,date) = 1

is still running 1min25seconds later after typing this message (Edit: Final execution time: 7m4s). Check the actual execution plans for each to see why one is "horrible".

modified on Thursday, March 24, 2011 6:01 AM

Wendelius · modified on Thursday, March 24, 2011 6:01 AM

Thanks for the info. Wondering why your test takes so long. I ran the same statements and both execution times were <1 sec... Also the plan seemed quite good... :confused:

The need to optimize rises from a bad design.My articles[^]

rakeshs312 · modified on Thursday, March 24, 2011 6:01 AM

Thanks verymuch for all

Wendelius · modified on Thursday, March 24, 2011 6:01 AM

One possible reason could be if you have either statistics io or statistics time on in Management Studio. This would cause a flood in the messages tab since every insert is reported separately to Studio. This, of course, won't happen with CTE which is handled as a single statement thus reporting only one statistics.

The need to optimize rises from a bad design.My articles[^]