July 23, 2003 at 12:16 am
Hi All,
Please help me.
Say you have 2 dates i.e 01-Jun-2003' and 30-Jun-2003'. I need to find how many fridays exists between these 2 dates in a single query.
Thanks,
Anu
Edited by - Anupama on 07/23/2003 01:28:01 AM
Edited by - Anupama on 07/23/2003 01:29:19 AM
July 23, 2003 at 6:23 am
You could create a UDF that accepts two dates (and the day of week you want to count between the two dates) and returns the number of the count. You then use the UDF in your query.
Joseph
July 23, 2003 at 8:28 am
DECLARE@date1 datetime,
@date2 datetime,
@Weekday int
SET @Date1 = '01-Jun-2003'
SET @Date2 = '30-Jun-2003'
SET @Weekday = 6 --Fri
SET @Date1 = (CASE WHEN DATEPART(weekday,@Date1) > @Weekday THEN DATEADD(day,7-(DATEPART(weekday,@Date1)-@Weekday),@Date1)
WHEN DATEPART(weekday,@Date1) < @Weekday THEN DATEADD(day,@Weekday-DATEPART(weekday,@Date1),@Date1)
ELSE @Date1
END)
SET @Date2 = (CASE WHEN DATEPART(weekday,@Date2) > @Weekday THEN DATEADD(day,@Weekday-DATEPART(weekday,@Date2),@Date2)
WHEN DATEPART(weekday,@Date2) < @Weekday THEN DATEADD(day,(@Weekday-DATEPART(weekday,@Date2)-7),@Date2)
ELSE @Date2
END)
SELECT DATEDIFF(week,@Date1,@Date2)+1
Edited by - davidburrows on 07/24/2003 02:13:24 AM
Far away is close at hand in the images of elsewhere.
Anon.
July 23, 2003 at 3:31 pm
Duplicate thread!! Solution can be written in a single select statement :
SELECT
(DATEDIFF(d, <start date>, <end date>)/7) +
CASE WHEN DATEPART(dw, <start date>) = 6
OR DATEPART(dw, <end date>) = 6
THEN 1
ELSE 0
July 24, 2003 at 2:34 am
Not quite NPeeters, try it with '2003-05-01' to '2003-05-31', yours returns 4 when it should be 5.
Far away is close at hand in the images of elsewhere.
Anon.
July 24, 2003 at 2:36 am
And remember if you have an SS2k with a setting other then U.S. English, you have to verify that DATEPART for Friday actually is 6.
See BOL 'DATEFIRST':
quote:
-- SET DATEFIRST to U.S. English default value of 7.SET DATEFIRST 7
GO
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
-- January 1, 1999 is a Friday. Because the U.S. English default
-- specifies Sunday as the first day of the week, DATEPART of 1/1/99
-- (Friday) yields a value of 6, because Friday is the sixth day of the
-- week when starting with Sunday as day 1.
SET DATEFIRST 3
-- Because Wednesday is now considered the first day of the week,
-- DATEPART should now show that 1/1/99 (a Friday) is the third day of the -- week. The following DATEPART function should return a value of 3.
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
Regards, Hans!
July 24, 2003 at 6:22 am
This one should work. It is single query.
You can change number (6) to number of day that you need to count. Friday = 6 when SET DATEFIRST 7.
SELECT
(DATEDIFF(d, @startdate, @enddate)/7)
+ CASE
WHEN DATEPART(dw, @startdate) = (6)
OR DATEPART(dw, @enddate) = (6) THEN 1
WHEN
CASE
WHEN DATEPART(dw, @startdate) < (6) THEN DATEPART(dw, @startdate) - (6)
ELSE - (6) - (7 - DATEPART(dw, @startdate))
END
+
CASE
WHEN DATEPART(dw, @enddate) <= (6) THEN (6) - DATEPART(dw, @enddate)
ELSE (6) - DATEPART(dw, @enddate) + 8
END < 0 THEN 0
ELSE 1
END
Edited by - mstric on 07/24/2003 06:28:21 AM
July 24, 2003 at 6:52 am
mstric:
Actually trying with @startdate = '2003-05-03' and @enddate = '2003-05-31' gives 5 fridays... So it doesn't work either :/
July 24, 2003 at 7:06 am
Mine works, it gives 4 fridays for 2003-05-03 to 2003-05-31, providing friday is weekday = 6.
Also my solution (although not tested) should work for any date range and any selected weekday.
Far away is close at hand in the images of elsewhere.
Anon.
July 24, 2003 at 8:29 am
I made some corrections... I tried to create single query.
SELECT
(DATEDIFF(d, @startdate, @enddate)/7)
+ CASE
WHEN DATEPART(dw, @startdate) = (6)
OR DATEPART(dw, @enddate) = (6) THEN 1
WHEN
CASE
WHEN DATEPART(dw, @startdate) <= (6) THEN DATEPART(dw, @startdate) - (6)
ELSE - (6) - (7 - DATEPART(dw, @startdate))
END
+
CASE
WHEN DATEPART(dw, @enddate) <= (6) THEN (6) - DATEPART(dw, @enddate)
ELSE (6) - 7 + DATEPART(dw, @enddate)
END > 0 THEN 1
ELSE 0
END
July 24, 2003 at 9:05 am
Hi.
Thought to also give my 2 cents independent of what your @@DATEFIRST settings are...
SELECT (DATEDIFF(d, @SD , @ED)) / 7 +
CASE WHEN ((DATEPART(dw, @SD ) + @@DATEFIRST +1)% 7 > (DATEPART(dw, @ED) + @@DATEFIRST +1)% 7)
OR (DATEPART(dw, @SD ) + @@DATEFIRST +1)% 7 = 0
THEN 1
ELSE 0
END
Regards, Hans.
P.S @SD = StartDate. @ED = EndDate
Range is inclusive.
Edited by - hanslindgren on 07/24/2003 09:06:32 AM
July 24, 2003 at 9:35 am
Yes - this is a duplicate thread. I like to use
select ((1+datediff(d, <StartDate>, <EndDate>)+datepart( dw, <StartDate> ) ) / 7 )
- case when datepart( dw, <StartDate> ) > 6 then 1 else 0 end
Of course - this depends on Friday being 6. You could make it more generic by replacing the first '1' with (7-<weekday in question>) and '> 6' with > <weekday in question>. By making that change, you wouldn't have to worry about whether Friday is day 5 or 6. If it were 5, the statement would become..
select ((2+datediff(d, <StartDate>, <EndDate>)+datepart( dw, <StartDate> ) ) / 7 )
- case when datepart( dw, <StartDate> ) > 5 then 1 else 0 end
Guarddata-
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply