How to find date of last Friday

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to find date of last Friday

Author

Topic

karthick.amace
Starting Member

23 Posts

Posted - 2010-08-15 : 10:41:19

hey bros,
How can I find date of last friday and it won't mind watever date you giving as I/P of this week.

- Karthick

------------------------------------------------------
"Desire makes what you wants to do"

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-08-15 : 11:26:28

Review this code:

Declare @someFriday datetime
,@currentDate datetime;

Set @someFriday = '20100101';
Set @currentDate = dateadd(day, -2, getdate());

Select @currentDate
,datename(weekday, @currentDate)
,dateadd(week, datediff(week, @someFriday, @currentDate), @someFriday)
,datename(weekday, dateadd(week, datediff(week, @someFriday, @currentDate), @someFriday))
,dateadd(week, datediff(week, @someFriday, @currentDate) - 1, @someFriday)
,datename(weekday, dateadd(week, datediff(week, @someFriday, @currentDate) - 1, @someFriday));

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-15 : 12:01:25

[code]DECLARE @Date datetime
SET @Date = '11 May 2010'--Example date passed

SELECT CASE WHEN DATEADD(dd,(DATEDIFF(dd,0,@Date)/7)* 7,0) + 5 < @Date THEN DATEADD(dd,(DATEDIFF(dd,0,@Date)/7)* 7,0) + 5 ELSE DATEADD(dd,(DATEDIFF(dd,0,@Date)/7)* 7,0) -3 END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-15 : 17:20:29

Keep it simple!

DECLARE	@Sample TABLE
	(
		theDate DATETIME
	)

INSERT	@Sample
SELECT	'20100805' UNION ALL	-- Thursday
SELECT	'20100806' UNION ALL	-- Friday
SELECT	'20100807' UNION ALL	-- Saturday
SELECT	'20100808' UNION ALL	-- Sunday
SELECT	'20100809' UNION ALL	-- Monday
SELECT	'20100810' UNION ALL	-- Tuesday
SELECT	'20100811' UNION ALL	-- Wednesday
SELECT	'20100812' UNION ALL	-- Thursday
SELECT	'20100813' UNION ALL	-- Friday
SELECT	'20100814'		-- Saturday

SELECT	theDate,
	DATENAME(WEEKDAY, theDate) AS theWeekdayName,
	DATEADD(DAY, DATEDIFF(DAY, 4, theDate) / 7 * 7, 4) AS [Including same friday],
	DATEADD(DAY, DATEDIFF(DAY, 5, theDate) / 7 * 7, 4) AS [Not including same friday]
FROM	@Sample

N 56°04'39.26"
E 12°55'05.63"

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-08-15 : 18:33:28

Peso,

Thanks - yours is actually a bit safer than mine. Mine is based off the differences in the week boundary - which does introduce some issues when getting the next/previous values.

Yours does not have this limitation - and I keep forgetting about that issue, mostly because I don't have to do this type of calculation often enough to remember it :)

Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 03:29:56

Thank you!
There is also a caveat using DATEPART and WEEK (and/or weekday), and that's beause they are reliant on the current SET DATEFIRST setting.

N 56°04'39.26"
E 12°55'05.63"

karthick.amace
Starting Member

23 Posts

Posted - 2010-08-18 : 07:22:38

can anybody explain me calculation what Peso done ...

------------------------------------------------------
"Desire makes what you wants to do"

karthick.amace
Starting Member

23 Posts

Posted - 2010-08-20 : 00:37:35

no one knows the calculation peso done?

------------------------------------------------------
"Desire makes what you wants to do"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-20 : 00:58:46

it is finding the number of days from a reference date (a Friday). The reference data is 4 which is 1900-01-05 (Date 0 is 1900-01-01)


select	convert(datetime, 4),
	datename(weekday, convert(datetime, 4))

KH
[spoiler]Time is always against us[/spoiler]

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-08-22 : 01:32:35

quote:
Originally posted by karthick.amace

hey bros,
How can I find date of last friday and it won't mind watever date you giving as I/P of this week.

- Karthick

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[fn_previousDate](@days smallint,@date smalldatetime)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @dateString varchar(50)
SELECT @dateString = CONVERT( DATETIME,@date,103) - @days
return SUBSTRING(CAST(@dateString as varchar),1,12)
END

SELECT dbo.fn_previousDate(5,getdate())

--- answer : is this what you want?
Aug 17 2010

(1 row(s) affected)

------------------------------------------------------
"Desire makes what you wants to do"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-22 : 12:12:57

quote:
Originally posted by glendcruz

quote:
Originally posted by karthick.amace

hey bros,
How can I find date of last friday and it won't mind watever date you giving as I/P of this week.

- Karthick

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[fn_previousDate](@days smallint,@date smalldatetime)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @dateString varchar(50)
SELECT @dateString = CONVERT( DATETIME,@date,103) - @days
return SUBSTRING(CAST(@dateString as varchar),1,12)
END

SELECT dbo.fn_previousDate(5,getdate())

--- answer : is this what you want?
Aug 17 2010

(1 row(s) affected)

------------------------------------------------------
"Desire makes what you wants to do"

why you convert dates to varchar?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-08-22 : 13:01:24

quote:
Originally posted by glendcruz

quote:
Originally posted by karthick.amace

hey bros,
How can I find date of last friday and it won't mind watever date you giving as I/P of this week.

- Karthick

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[fn_previousDate](@days smallint,@date smalldatetime)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @dateString varchar(50)
SELECT @dateString = CONVERT( DATETIME,@date,103) - @days
return SUBSTRING(CAST(@dateString as varchar),1,12)
END

SELECT dbo.fn_previousDate(5,getdate())

--- answer : is this what you want?
Aug 17 2010

(1 row(s) affected)

------------------------------------------------------
"Desire makes what you wants to do"

There are multiple problems with this approach:

First, using a function could cause performance issues if this is used in a query - either as a column in the select or in the where clause.

Second, converting to varchar and back to datetime is not necessary. And, your conversion does not specify the length for the varchar return. Using just the date functions is much faster.

Third, the OP does not know how many days to go back - which is what we want to be able to get. If this is run today, the number of days back will be different from tomorrow or yesterday.

And finally, if you want @days back from today - this is much simpler and will perform much better:

DATEADD(day, DATEDIFF(day, 0, getdate()) - @days, 0)

Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-22 : 14:08:40

quote:
Originally posted by jeffw8713

DATEADD(day, DATEDIFF(day, 0, getdate()) - @days, 0)

DATEADD(day, DATEDIFF(day, @days, getdate()), 0)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources