| Author |
 Topic  |
|
|
Hommer
Aged Yak Warrior
693 Posts |
 Posted - 03/11/2013 : 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
India
47040 Posts |
Posted - 03/11/2013 : 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
693 Posts |
Posted - 03/11/2013 : 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
India
47040 Posts |
Posted - 03/11/2013 : 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
693 Posts |
Posted - 03/11/2013 : 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
India
47040 Posts |
Posted - 03/11/2013 : 15:11:16
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 03/11/2013 : 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
693 Posts |
Posted - 03/12/2013 : 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... |
Edited by - Hommer on 03/12/2013 12:10:51 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 03/12/2013 : 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)
USA
6997 Posts |
Posted - 03/12/2013 : 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
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 03/12/2013 : 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 :) |
Edited by - James K on 03/12/2013 14:30:44 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 03/12/2013 : 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 |
|
|
| |
Topic |
|
|
|
Last Friday
