Another issue has cropped up in my Oracle to SQL Svr migration. Can anybody suggest the equivalent code for the Oracle NEXT_DAY function?
Here is a sample of how Oracle NEXT_DAY function works...
SELECT NEXT_DAY(sysdate, 'Sunday') from dual ;
where sysdate is Oracle for GetDate() of SQL Svr. Forget about "from dual". So, what NEXT_DAY is doing is that it is returning the next coming date on which a Sunday will fall, if sysdate = 07 Dec 2004.
You'll have to write this youself, as there is no immediate equivalent.
You can pretty easily create a user-defined function that exactly mimics oracle NEXT_DAY()
You would have to use the SQL function datename(weekday, date)
and the SQL function dateadd(day, 1, date).
Loop round a max of seven (six?) times using dateadd to add a day to your date, and then using datename to get the day description which you could then compare with your input parameter. return the appropraite date when a match is found.