0

Looking for code in SQL SERVER 2012 to find out Next occurrence of 'WEDNESDAY' for all ORDERDATEs from ORDERS Table. I came up with the following, but it just gives the Orderdate + 7 days. Kindly help me on this.

SELECT DATEADD(DD,7, CONVERT(Datetime, OrderDate, 101))
FROM Orders
Princess S
  • 89
  • 1
  • 1
  • 9

1 Answers1

1

You can use this:

SELECT CASE WHEN DATENAME(WEEKDAY,OrderDate) = 'Wednesday' 
       THEN DATEADD(DAY,7,OrderDate)
       ELSE DATEADD(DAY,(18-(@@DATEFIRST+DATEPART(WEEKDAY,OrderDate)))%7,OrderDate)
       END
FROM Orders
Lamak
  • 69,480
  • 12
  • 108
  • 116
  • But this query is returning '04/01/2009' (mm/dd/yyyy) even though the date is past 1st april. Means - even though the date is 2,3,4 of that week it is returning '04/01/2009' http://www.timeanddate.com/calendar/?year=2009 – Princess S Jun 25 '13 at 16:06
  • Now I am getting it right. Thank you. But when the date itself is '04/01/2009' its returning the same date. But am looking for the next occurrence, So it should be returning me '04/08/2009' for '04/01/2009' – Princess S Jun 25 '13 at 16:37
  • @PrincessS Well, now this is an ugly query, but I think it works. – Lamak Jun 25 '13 at 16:48
  • @PrincessS yup, there was a missing `END` there (pretyy easy to spot, though) – Lamak Jun 25 '13 at 16:57
  • I got it. Thanks for your patience and help. I am very much new to SQL. Just started learning myself, haven't came across CASE yet. – Princess S Jun 25 '13 at 17:06