SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Last Friday
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

794 Posts

Posted - 03/11/2013 :  12:33:14  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/11/2013 :  12:40:01  Show Profile  Reply with Quote
you can just use

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

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

Go to Top of Page

Hommer
Aged Yak Warrior

794 Posts

Posted - 03/11/2013 :  13:18:09  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/11/2013 :  14:09:47  Show Profile  Reply with Quote
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/

Go to Top of Page

Hommer
Aged Yak Warrior

794 Posts

Posted - 03/11/2013 :  14:36:11  Show Profile  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/11/2013 :  15:11:16  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/11/2013 :  16:07:48  Show Profile  Reply with Quote
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
Go to Top of Page

Hommer
Aged Yak Warrior

794 Posts

Posted - 03/12/2013 :  12:10:02  Show Profile  Reply with Quote
Wow, that is fantastic!

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

I got the Mondays by 17530101...

Edited by - Hommer on 03/12/2013 12:10:51
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 03/12/2013 :  12:19:46  Show Profile  Reply with Quote
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/12/2013 :  13:29:49  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 03/12/2013 :  14:30:08  Show Profile  Reply with Quote
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 :)

Edited by - James K on 03/12/2013 14:30:44
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/12/2013 :  22:16:17  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000