x

sql for day of the week in the future

I need to script and find the date for tuesday - or dw = 3 when today is thursday...or friday...

but i need to use the tue date as a constant value to place in a report that will be used for an order date.

more ▼

asked Oct 16 '11 at 02:03 PM in Default

siera_gld gravatar image

siera_gld
1k 74 80 83

So are you wanting a query that will, in effect, return "Next Tuesday's date"?
Oct 16 '11 at 03:02 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

simplest way without complicated arithmetic operations is

declare @dte    date

select  @dte = '2011-10-17'

select  dateadd(day,
       case  datename(weekday, @dte)
         when 'Sunday' then 2
         when 'Monday' then 1
         when 'Tuesday' then 7
         when 'Wednesday' then 6
         when 'Thursday' then 5
         when 'Friday' then 4
         when 'Saturday' then 3
         end,
       @dte)
more ▼

answered Oct 16 '11 at 07:16 PM

Squirrel gravatar image

Squirrel
1.5k 1 2 4

(comments are locked)
10|1200 characters needed characters left
SET DATEFIRST 1
DECLARE @dt DATE
SET @dt = '2011-05-13'
SELECT DATEADD(day,
    CASE 
      WHEN datepart(weekday,@dt)<2 
      THEN 2-datepart(weekday,@dt) 
      ELSE 9-datepart(weekday,@dt) 
    END,
@dt)
more ▼

answered Oct 16 '11 at 10:53 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

(comments are locked)
10|1200 characters needed characters left
all of the time - so it should not matter if it's thur or friday at the time the query id executed, tue should be the return date.
more ▼

answered Oct 16 '11 at 03:09 PM

siera_gld gravatar image

siera_gld
1k 74 80 83

(comments are locked)
10|1200 characters needed characters left

I know this is very late, but I was looking for the same answer today and found this:

select getdate(),dateadd(week, datediff(week, 0, getdate()), 9)

I am including GetDate() in the results only to prove that if GetDate() changes, the returned value will always be next Wednesday (in this example). Change the 9 to an 8 for Tuesday.

more ▼

answered Aug 22 '13 at 12:46 AM

DannyC gravatar image

DannyC
0 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x673
x25

asked: Oct 16 '11 at 02:03 PM

Seen: 859 times

Last Updated: Aug 22 '13 at 02:08 PM