SQL . The SP or function should calculate the next

问题:

I need to write a store procedure that will return a next friday date on a given date? for example - if the date is 05/12/2011, then it should return next friday date as 05/13/2011. If you pass, 05/16/2011, then it should return the date is 5/20/2011 (Friday). If you pass friday as the date, then it should return the same date.

回答1:

I'd make this a scalar UDF as it is easier to consume the output.

CREATE FUNCTION dbo.GetNextFriday(
@D DATETIME
)
RETURNS DATETIME 
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN DATEADD(DAY,(13 - (@@DATEFIRST + DATEPART(WEEKDAY,@D)))%7,@D)
END

回答2:

This is for SQL Server 2008. To use in 2005, just change the date fields to your preference for datetime to date conversions. It also assumes you are not changing the default week begin value.

DECLARE @PassedDate date = '5/21/2011';
SELECT DATEADD(DAY,(CASE DATEPART(DW,@PassedDate) WHEN 7 THEN 6 ELSE 6 - DATEPART(DW,@PassedDate) END),@PassedDate);

回答3:

Great solutions here, but I also recommend looking at time tables: you can generate them easily in Analysis server, and they can be indexed to be very fast, giving you lots of easy ways to get next week days (among other things).

You can find out more about them here

In our case, the same solution would be

Select MIN(PK_Date) from Time Where PK_Date > @SomeDate AND Day_Of_Week= 6

And of course when you're doing this for a large recordset, you can also do joins for maximum speed & efficiency.