Last Friday - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Last Friday

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-03-11 : 12:33:14

Hi,

I went through the script library posts starting from this one
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
and start of the week function...

But I want to know if there are other easier ways.

I want to dynamically generate last Friday's date to use as ending date for a select query.

I came accross following two solutions. They both worked fine only if last Friday, i.e. 03/08/2013 is in the refund_date.

Select top 1 refund_date
from mytable where DATEPART(Weekday, refund_date + @@DATEFIRST -1) = 5
order by refund_date desc

Select top 1 refund_date
from mytable where Datediff(day, '19000105',refund_date )%7 = 0
order by refund_date desc --1900-01-05 is a Friday

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 12:40:01

you can just use

SELECT DATEADD(dd,((DATEDIFF(dd,0,GETDATE())/7) * 7),-3)

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

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-03-11 : 13:18:09

Thank you for the reply.

These three did not pass the test:

SELECT DATEADD(dd,((DATEDIFF(dd,0,'2013-03-03')/7) * 7),-3)--got 2/22 but looking for 3/1
SELECT DATEADD(dd,((DATEDIFF(dd,0,'2013-03-09')/7) * 7),-3)--got 3/1 not 3/8
SELECT DATEADD(dd,((DATEDIFF(dd,0,'2013-03-16')/7) * 7),-3)--got 3/8 but not 3/15

Maybe I did not make myself clear. I want the lastest last Friday.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 14:09:47

Handled that as well


DECLARE @date datetime
SET @date='2013-03-15' -- try other values also here

SELECT  CASE 
WHEN DATEDIFF(dd,0,@Date) % 7 >=4 THEN @date -(DATEDIFF(dd,0,@Date) % 7 - 4)
ELSE DATEADD(dd,((DATEDIFF(dd,0,@Date)/7) * 7),-3)
END

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

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-03-11 : 14:36:11

Thank you so much! Now I think this should work.

quote:
Originally posted by visakh16

Handled that as well

DECLARE @date datetime
SET @date='2013-03-15' -- try other values also here

SELECT CASE
WHEN DATEDIFF(dd,0,@Date) % 7 >=4 THEN @date -(DATEDIFF(dd,0,@Date) % 7 - 4)
ELSE DATEADD(dd,((DATEDIFF(dd,0,@Date)/7) * 7),-3)
END

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 15:11:16

welcome

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2013-03-11 : 16:07:48

This is a fairly simple method:

select
	a.[Date],
	Friday = dateadd(dd,(datediff(dd,'17530105',a.DATE)/7)*7,'17530105')
from
	( --Test Data
	select [DATE] = getdate()	union all
	select '20130303'		union all
	select '20130309'		union all
	select '20130316'
	) a

Results:

Date                    Friday
----------------------- -----------------------
2013-03-11 16:05:42.600 2013-03-08 00:00:00.000
2013-03-03 00:00:00.000 2013-03-01 00:00:00.000
2013-03-09 00:00:00.000 2013-03-08 00:00:00.000
2013-03-16 00:00:00.000 2013-03-15 00:00:00.000

Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

CODO ERGO SUM

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-03-12 : 12:10:02

Wow, that is fantastic!

By the way, what is this 17530105, day of week anchor/magic number?

I got the Mondays by 17530101...

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-12 : 12:19:46

It is not really a magic number, January 5, 1753 happens to be a Friday. You can use other Friday's as well, for example, this would work just the same for all practical purposes:

...
Friday = dateadd(dd,(datediff(dd,'19000105',a.DATE)/7)*7,'19000105')
...

January 5, 1753 happens to be the earliest Friday that DATETIME datatype can represent.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2013-03-12 : 13:29:49

quote:
Originally posted by James K

It is not really a magic number, January 5, 1753 happens to be a Friday. You can use other Friday's as well, for example, this would work just the same for all practical purposes:
...
Friday = dateadd(dd,(datediff(dd,'19000105',a.DATE)/7)*7,'19000105')
...
January 5, 1753 happens to be the earliest Friday that DATETIME datatype can represent.

I use 17530105 for this because it is the earliest possible datetime Friday, and because the algorithim does not return correct results if the the date is before the date you use as your base.

For example, dates before 1900-01-05 will not return correct results if you use 19000105. Probably does not matter for most applications, but it is no harder to use 17530105 than to use 19000105.

CODO ERGO SUM

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-12 : 14:30:08

Michael, so very true, and I had that in the back of my mind (although I must admit, not in the front of my mind), when I inserted the disclaimer "this would work just the same for all practical purposes"

But, I should not have said "all", obviously :)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2013-03-12 : 22:16:17

quote:
Originally posted by James K

Michael, so very true, and I had that in the back of my mind (although I must admit, not in the front of my mind), when I inserted the disclaimer "this would work just the same for all practical purposes"

But, I should not have said "all", obviously :)

That's all OK with me as long as the discussion is civil.

Here is one on a similar subject that got really nasty:
Last Sunday of a month in sql
http://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources