select * from orders where orderDate between funcA() and funcB()
CREATE FUNCTION [dbo].[LastWeek](@FirstDate CHAR(3))
RETURNS DATETIME
begin
declare @result DATETIME
select @result = (
SELECT
CASE @FirstDate
WHEN 'mon' THEN
CONVERT(NVARCHAR,DATEADD(DAY,- (datepart(WEEKDAY,GETDATE())+6),GETDATE()),112)
ELSE
CASE DATEPART(WEEKDAY,GETDATE())
WHEN 7 THEN
CONVERT(NVARCHAR,DATEADD(DAY,- datepart(WEEKDAY,GETDATE()+7),GETDATE()),112)
ELSE
CONVERT(NVARCHAR,DATEADD(DAY,- (datepart(WEEKDAY,GETDATE())+7),GETDATE()),112)
END
END
)
RETURN @result
END
SET DATEFIRST 1
SELECT dbo.LastWeek('mon')
SELECT dbo.LastWeek('sun')