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

SQL Server 2000 Forums

Transact-SQL (2000)

Last Friday

Author

Topic

TexDec
Starting Member

22 Posts

Posted - 2002-07-02 : 10:34:40

Is there a way I can work out what last Friday's date was?
I dont't have a table of dates, nor do I really want to create one just for this.
What I'm trying to do is

SELECT MAX somedate
FROM somewhere
WHERE somedate < GETDATE()
AND DATENAME(dw,somedate) = 'FRIDAY'

I'm hoping there's some system table that I can use to achieve this.

Thanks in advance

Declan K

The only Keane we need is Robbie

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 10:51:03

hehe, this is what you call 'fugly' but it works

select
case
when (select getdate() having DATENAME(dw,getdate()) = 'Friday') <> '' then getdate()
when (select getdate() having DATENAME(dw,dateadd(dd,-1,getdate())) = 'Friday') <> '' then dateadd(dd,-1,getdate())
when (select getdate() having DATENAME(dw,dateadd(dd,-2,getdate())) = 'Friday') <> '' then dateadd(dd,-2,getdate())
when (select getdate() having DATENAME(dw,dateadd(dd,-3,getdate())) = 'Friday') <> '' then dateadd(dd,-3,getdate())
when (select getdate() having DATENAME(dw,dateadd(dd,-4,getdate())) = 'Friday') <> '' then dateadd(dd,-4,getdate())
when (select getdate() having DATENAME(dw,dateadd(dd,-5,getdate())) = 'Friday') <> '' then dateadd(dd,-5,getdate())
when (select getdate() having DATENAME(dw,dateadd(dd,-6,getdate())) = 'Friday') <> '' then dateadd(dd,-6,getdate())
end

-----------------------
Take my advice, I dare ya

TexDec
Starting Member

22 Posts

Posted - 2002-07-02 : 11:06:56

Ahhh. Jackie Joyner-Kersee code......

Ugly, but it will run...

Cheers

Declan K

The only Keane we need is Robbie

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 11:10:37

quote:
Ahhh. Jackie Joyner-Kersee code......

I prefer to call it my really ugly looking code.. but if you prefer

-----------------------
Take my advice, I dare ya

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-02 : 11:30:16

If @dayno is the number returned by DATEPART(dw) for the day of the week you are trying to get, then:
DATEADD(day, -1 - (DATEPART(dw, @dt) + 6 - @dayno)%7, @dt)
will give the same time on the previous (before @dt) occurrence of that day.
This works for any setting of @@DATEFIRST since @dayno depends on @@DATEFIRST too.
In the common case where @@DATEFIRST = 7, DATEPART(dw) returns 6 for Friday, so this simplifies to
DATEADD(day, -1 - DATEPART(dw, @dt)%7, @dt)

BTW, if you want it to return @dt when @dt is the target day, rather than the previous one, use
DATEADD(day, - (DATEPART(dw, @dt) + 7 - @dayno)%7, @dt)

Edited by - Arnold Fribble on 07/02/2002 11:36:05

TexDec
Starting Member

22 Posts

Posted - 2002-07-02 : 11:58:41

Arnold
Cheers for this excellent piece of code. It works perfectly, even on our server where weeks begin on Saturday.
I have worked my way through it and understand what each piece is doing, except the Modulo %7.

Using an excert from the code....
select -1-(DATEPART(dw, GETDATE())+6-7)%7
and
select -1-(DATEPART(dw, GETDATE())+6-7)

both return the same result.
Can you explain why you added this operator.

just trying to learn a little ;-)
Thanks

Declan K

The only Keane we need is Robbie

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-07-02 : 12:13:28

Try this:

SET DATEFIRST 5
DECLARE @dt datetime
SET @dt = CURRENT_TIMESTAMP --Replace CURRENT_TIMESTAMP with your date
SELECT @dt + (1-DATEPART(dw, @dt)) AS 'Last Friday'

--
HTH,
Vyas
http://vyaskn.tripod.com

Edited by - vyaskn on 07/02/2002 12:14:55

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-02 : 12:26:44

You want a day [1..7] days ago. If you're basing the calculation on a target day number @dayno in the range [1..7] and a starting day number DATEPART(dw, @dt) in the same range, then the result of DATEPART(dw, @dt) - @dayno — which when subtracted from @dt gives you a fixed weekday — will be in the range [-6..6].
So the % 7 limits it to [0..6] i.e. makes sure that we're picking the previous occurrence, not sometimes the next.
The +6 (or +7) within the parentheses just ensures that the left operand of % stays non-negative — % can return -ve values if its left operand is -ve.

Edited by - Arnold Fribble on 07/02/2002 12:28:17

TexDec
Starting Member

22 Posts

Posted - 2002-07-02 : 12:54:18

All
Thanks for all your help and suggestions.

We can now boil this problem, and its solution, down to a very simple statement.

Prob: I want to return the last day of last week. (in my case Friday, as we start our weeks on Saturday).

Solution:
The last day of last week is today minus a number of days.
The number of days to subtract is always equal to today's position in this week (think of the last day of last week as day zero of this week).
So, the last day of last week (ignoring time piece) is always

SELECT GETDATE()-DATEPART(dw,GETDATE())

Note: this works regardless of the value of DATEFIRST

Once again, thanks for all the help.

Declan K

The only Keane we need is Robbie

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-07-02 : 12:59:12

>> The only Keane we need is Robbie

You Irish? :-)

--
HTH,
Vyas
http://vyaskn.tripod.com

TexDec
Starting Member

22 Posts

Posted - 2002-07-02 : 13:04:06

With a name like Declan? No, I'm Polish... ;-)

Declan K

The only Keane we need is Robbie

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-07-02 : 13:15:08

I know one Robbie Keane from the Irish footy team

--
HTH,
Vyas
http://vyaskn.tripod.com