Calculating the Last Friday of any date
-
Hi
I need to be able to generate the last Friday after passing on a date, say for example using today’s date, I want to know the date for the very last Friday, so if I can the query today it should be 16/03/2007 and if I run the query next week Saturday It should return 23/03/2007.
I look forward to your reply.
-
Hi there,
You can do this with DateAdd/DateDiff.. somthing like:
CREATE PROCEDURE sp_GetPreviousFriday
@date DATETIME,
@lastFriday DATETIME OUT
AS
DECLARE @rootDate DATETIME;
DECLARE @dayOfWeek INT;
SET @rootDate = CAST('1990/01/05' as Datetime)
SELECT @dayOfWeek = DATEPART(weekday, @date)
print @dayOfWeek
IF(@dayOfWeek < 6)
BEGIN
SELECT @lastFriday = DATEADD(WEEK, DATEDIFF(week, @rootDate, @date) -1, @rootDate)
END
ELSE
BEGIN
IF(@dayOfWeek = 6)
BEGIN
SET @lastFriday = @date
END
ELSE
BEGIN
SELECT @lastFriday = DATEADD(WEEK, DATEDIFF(week, @rootDate, @date), @rootDate)
END
END
GO
DECLARE @lastFriday DATETIME
DECLARE @date DATETIME
SET @date = CAST('2007/03/19' as Datetime)
EXECUTE sp_GetPreviousFriday @date, @lastFriday OUTPUT
SELECT @date as [Date], @lastFriday as [LastFriday]
SET @date = CAST('2007/03/24' as Datetime)
EXECUTE sp_GetPreviousFriday @date, @lastFriday OUTPUT
SELECT @date as [Date], @lastFriday as [LastFriday]
- James
--
James Moore
Red Gate Software Ltd -
Hi ,
Try this Query its working......
set datefirst 1
select dateadd(dd,(case when datepart(dw,convert(datetime,'2007-03-15')) < 5 then
-(7 - (5 - datepart(dw,convert(datetime,'2007-03-15'))))
else
- (datepart(dw,convert(datetime,'2007-03-15')) - 5)
end
),convert(datetime,'2007-03-15'))
Regards ,
Amit Gupta
-
You can use the Start of Week Function, F_START_OF_WEEK, on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
Or you can do it directly with this statment:
Select LastFriday = dateadd(dd,(datediff(dd,-53686,@MyDate)/7)*7,-53686)
You didn't say what you wanted if the date is Friday. Do you want that date, or the prior Friday?
-
check your dateformat so you know which day is which. For example, for me in the US, Sunday is 1.
The previous Friday is simple math. So for today, I subtract 4 days. A cASE statement will do it, but might wrap it in a function
select
case
when datepart( dw, getdate()) = 1
then dateadd( d, -2, getdate())
when datepart( dw, getdate()) = 2
then dateadd( d, -3, getdate())
when datepart( dw, getdate()) = 3
then dateadd( d, -4, getdate())
when datepart( dw, getdate()) = 4
then dateadd( d, -5, getdate())
when datepart( dw, getdate()) = 5
then dateadd( d, -5, getdate())
when datepart( dw, getdate()) = 6
then getdate()
when datepart( dw, getdate()) = 7
then dateadd( d, -1, getdate())
end 'Friday'
-
No need to check dateformat if you use one of these:
DECLARE @mydate datetime
SET @mydate='20070209'
SELECT DATEADD(day, (DATEDIFF (day, '19990101', @mydate) / 7) * 7, '19990101') as friday_including_mydate
SELECT DATEADD(day, (DATEDIFF (day, '19990102', @mydate) / 7) * 7, '19990101') as friday_before_mydate
These queries are independent on any regional (date and language) settings.
-
You code does not work for days before 1900-01-01. Notice in the code below that it returns a Friday that is later than @mydate.
The code I posted works for any datetime value, except for days before 1753-01-05, because there is no valid datetime before that date that is a Friday.
declare @mydate datetime select @mydate ='18000101' select Day_of_Week=datename(dw,@mydate)
SELECT DATEADD(day, (DATEDIFF (day, '19990101', @mydate) / 7) * 7, '19990101') as friday_including_mydate SELECT DATEADD(day, (DATEDIFF (day, '19990102', @mydate) / 7) * 7, '19990101') as friday_before_mydate
Results: Day_of_Week ------------------------------ Wednesday
(1 row(s) affected)
friday_including_mydate ------------------------------------------------------ 1800-01-03 00:00:00.000
(1 row(s) affected)
friday_before_mydate ------------------------------------------------------ 1800-01-03 00:00:00.000
(1 row(s) affected)
-
You're right, thanks for the warning. This code only works with @mydate newer than the date used in the select. I copied it from a piece code meant for dates > 1.1.2000 and forgot to mention it (actually, forgot about that fact absolutely).
Thanks again,
V.
-
Hi, This code is work only for finding the last friday of the particular month
Declare @Ldate DateTime
Declare @LWN int
Declare @RWN int
Set @RWN=6 -- Sunday=1, Monday=2........
SELECT @LDate=DATEADD(MONTH, 1, 1-DAY(GETDATE())+GETDATE())-1
SELECT @LWN=DatePART(dw,DATEADD(MONTH, 1, 1-DAY(GETDATE())+GETDATE())-1)
Declare @RDate DateTime
if(@LWN>@RWN)
set @RDate=@LDate-(@LWN-@RWN)
else
set @RDate=@LDate-((7-@RWN) + @LWN)
print @RDate
Regards,
Sudhakar.E
-
for the same scope i've used this function. is useful also to compute the previous friday.
CREATE FUNCTION Next_Friday
(@ref_date datetime)
RETURNS datetime
AS
BEGIN
declare @comp_date datetime
DECLARE @ddayVARCHAR (15)
declare @X varchar
SET @COMP_DATE = convert(varchar(2),datepart (mm,@REF_DATE)) + '/' + convert(varchar(2),datepart (dd,@REF_DATE)) ++ '/' + convert(varchar(4),datepart (yyyy,@REF_DATE))
SET @X = '1'
WHILE @X<>0
BEGIN
SELECT @dday = DATENAME(DW, @COMP_DATE)
IF @GIORNO = 'Friday'
set @X =0
else
set @comp_date = @comp_date - 1
END
RETURN ( @comp_date)
END
-
sorry, the line:
set @comp_date = @comp_date - 1
is wrong (good to compute the previous friday). use this
set @comp_date = @comp_date + 1
-
Hello,
Please try this query.
set datefirst 7
select dateadd(dw,-(datepart(dw,getdate())+1),getdate())
Thank you
Regards
Mohit
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply