July 29, 2010 at 11:26 pm
Not sure whether this is possible get number of Saturdays within a date range / two dates using Date function in SQL Server 2005.
Preferred not to use stored procedure or extra tables.
July 30, 2010 at 12:56 am
Quite Possible... But would want to know if this is some kind of Home-work, is it?
If yes, then can u please update us with what you have tried till now? even semi-cooked is fine..
July 30, 2010 at 1:17 am
Anyways, take this:
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @NumOfDays INT
SET @StartDate = '01-JUL-2010 00:00:00.000'
SET @EndDate = '31-JUL-2010 00:00:00.000'
SET @NumOfDays = DATEDIFF(DD,@StartDate , @EndDate) + 1 ;
WITH Tens AS
(
SELECT 1 N 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
),
HUNDREDS AS
(
SELECT T1.N FROM TENS T1 CROSS JOIN TENS T2
),
THOUSANDS AS
(
SELECT T1.N FROM HUNDREDS T1 CROSS JOIN HUNDREDS T2
),
Numbers AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RN FROM THOUSANDS
)
SELECT
DATEADD( DD,(RN - 1) , @StartDate ) SaturdayDates
FROM
Numbers
WHERE
RN <= @NumOfDays AND
DATENAME ( WEEKDAY, (DATEADD( DD,(RN - 1) , @StartDate )) ) = 'Saturday'
This will give the list of Saturdays between 2 days. Hope this helps. I will leave it to you to get the count of Saturdays between your dates.
Hope this helps.! 🙂
July 30, 2010 at 1:59 am
IMO , a calendar table is essential.
If your datefirst is sunday then
you can simply use "datediff(ww" , since that counts the number of 'boundaries' not calendar weeks
July 30, 2010 at 2:21 am
If you are just looking for the number of Saturdays within the date range (as opposed to a list of all the Saturdays within that range) you can achieve it with just a few lines of code and without any tables.
Remember that if @date is a Saturday, then datepart(dw,@date)=7
Using this knowledge, you can calculate the first saturday after the start date and the last saturday before the end date. For example, to get the first saturday before the start date:
declare @firstsaturday smalldatetime
set @firstsaturday=
case
when datepart(dw,@startdate)=7
then @startdate
else @startdate+7-datepart(dw,@startdate)
end
In case this is homework, I'll leave you to work out how to get the last saturday before the end date.
Having got your valuies for @firstsaturday and @lastsaturday,then to get the number of saturdays, take the integer difference between @firstsaturday and @lastsaturday, divide by 7, and then add 1 to the result.
The only gotcha is that if there is no saturday in the date range, the value for @firstsaturday will be greater than @lastsaturday, so you will need a case statement to cover this eventuality.
July 30, 2010 at 2:33 am
martin.whitton (7/30/2010)
Remember that if @date is a Saturday, then datepart(dw,@date)=7
Wrong , this is not a fact. @@DateFirst depends on regional settings.
From http://blog.sqlauthority.com/2007/04/22/sql-server-datefirst-and-set-datefirst-relations-and-usage/
SET LANGUAGE italian
GO
SELECT @@DATEFIRST
GO
----This will return result as 1(Monday)
SET LANGUAGE us_english
GO
SELECT @@DATEFIRST
GO
July 30, 2010 at 2:47 am
Dave, you're right - I should have clarified about regional settings.
But the fact remains that, whatever your regional settings, you can still use "datepart" to get next Saturday's date although the precise calculation will differ.
July 30, 2010 at 5:14 am
N 56°04'39.16"
E 12°55'05.25"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply