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, 2011 at 02:03 PM in Default

avatar image

siera_gld
1k 82 88 93

So are you wanting a query that will, in effect, return "Next Tuesday's date"?

Oct 16, 2011 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, 2011 at 07:16 PM

avatar image

Squirrel
2.7k 1 4 7

(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, 2011 at 10:53 PM

avatar image

Magnus Ahlkvist
20.9k 19 39 42

(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, 2011 at 03:09 PM

avatar image

siera_gld
1k 82 88 93

(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, 2013 at 12:46 AM

avatar 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.

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:

x1066
x987
x31

asked: Oct 16, 2011 at 02:03 PM

Seen: 1374 times

Last Updated: Aug 22, 2013 at 02:08 PM

Copyright 2016 Redgate Software. Privacy Policy