default date value of every Friday in SQL Server?

Question

In SQL Server 2008 i want to set a default date value of every Friday to show up in the column when i insert a new record?

ALTER TABLE myTable ADD CONSTRAINT_NAME DEFAULT GETDATE() FOR myColumn

Whats the best way to show every Friday?

I want the default value to be based on the now date then knowing that the next available date is 05-07/2013

I have the following:

dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate())

But when passing todays date, it gave me: 2013-06-28 which is actually LAST Friday!, it should be the up and coming Friday!

So you want the Default Value of some column to be the nearest upcoming Friday? — David Starkey, Jul 01 '13 at 17:57
http://stackoverflow.com/questions/5998084/get-the-most-recent-fridays-date-sql — Robert Harvey, Jul 01 '13 at 17:58
I want the default value to be based on the now date then knowing that the next available date is `05-07/2013` — PriceCheaperton, Jul 01 '13 at 17:58

Mark Schultheiss · Accepted Answer · 2013-07-01T22:41:11.430

SELECT DATEADD(day,-3, DATEADD(week, DATEDIFF(week, 0, current_timestamp)+1, 0)) AS LastFridayDateOfWeek

Gets the last date of current week (sunday) then subtracts 3 from that to get Friday.

Replace current_timestamp if you need a different dates friday.

EDIT: I thought about this a bit, and if the above (Friday THIS WEEK, so for Saturday it gives the previous date) does not work, you could easily use a reference date set like so:

DATEADD(DAY,7 + DATEDIFF(day,'20100109',@checkDateTime)/7*7,'20100108') as FridayRefDate

Same thing but with no hard coded Friday/Saturday in it:

DATEADD(DAY,7 + DATEDIFF(day,DATEADD(wk, DATEDIFF(wk,0,@checkDateTime),5),@checkDateTime)/7*7,DATEADD(wk, DATEDIFF(wk,0,@checkDateTime), 4))

So for 20100109 is a Friday.

SET @checkDateTime = '2012-01-14 3:34:00.000'
SELECT DATEADD(DAY,7 + DATEDIFF(day,'20100109',@checkDateTime)/7*7,'20100108') as FridayRefDate

it returns "2012/1/20"

But for SET @checkDateTime = '2012-01-13 3:34:00.000' it returns "2012/1/13"

score 1 · Answer 2 · edited Jul 01 '13 at 18:33

1

If your current query gives you last Friday, the easiest thing to do is simply to add 7 to it:

select dateadd(d, 7-((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate())
------------------^

edited Jul 01 '13 at 18:33

David Starkey

1,840
3
32
48

answered Jul 01 '13 at 18:18

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Jul 01 '13 at 18:28

SELECT CONVERT(DATE, ( CASE WHEN DATEPART(dw, GETDATE()) - 6 <= 0 THEN DATEADD(dd, ( DATEPART(dw, GETDATE()) - 6 ) * -1, GETDATE()) ELSE DATEADD(dd, ( DATEPART(dw, GETDATE()) ) - 1, GETDATE()) END )) AS NearestFriday

Anon · Answer 4 · 2013-07-01T19:01:42.093

0

Just add 7 to the formula

SELECT DATEADD(dd,CAST(5-GETDATE() AS int)%7,GETDATE()+7)

To verify the formula:

WITH test AS (
  SELECT GETDATE() AS d UNION ALL
  SELECT DATEADD(dd,1,d)
  FROM test WHERE d < GETDATE() + 30
)
SELECT
 d                                  AS [input],
 DATEADD(dd,CAST(5-d AS int)%7,d+7) AS [output]
FROM test

To tweak the the formula, adjust the 5- and the +7

edited Jul 01 '13 at 19:01

answered Jul 01 '13 at 18:54

Anon

10,660
1
29
31

default date value of every Friday in SQL Server?

4 Answers4