NextDay

Hello everybody?

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 ;

Result:

NEXT_DAY
---
12-DEC-04

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.

Any help will be highly appreciated. ThxYou'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.

HTH, but I ain't coding it for you :-)

Bill|||Here is a non-looping algorithm:

declare @.TargetDay int
set @.TargetDay = 1 --Sunday
select dateadd(d, (@.TargetDay + 7-datepart(dw, getdate())) % 7, getdate())

TargetDay must be supplied as an integer, according to the settings of your @.@.DATEFIRST property. Normally, Sunday = 1, Monday = 2, etc.

Also, this returns 0 if the today is the TargetDay. Not sure exactly what you wanted in that case...|||Thx a lot lindman and bill..