Ufn_getweekfirstandendday gets the first and last day of the week on the specified date
ALTER FUNCTION [dbo]. [Ufn_getweekfirstandendday] (@tmpDate DATETIME)
RETURNS @tmpTable TABLE (
FirstDay DATETIME,
Endday DATETIME
)
As
BEGIN
INSERT into @tmpTable
SELECT A.firstday,b.endday from (
SELECT 1 as Id,dateadd (wk, DATEDIFF (wk,0, @tmpDate), 0) as FirstDay
) A
Left JOIN (
SELECT 1 as Id,dateadd (wk, DATEDIFF (wk,0, @tmpDate), 6) as Endday
) b
On a.id = b.ID
RETURN
END
Ufn_getmonthfirstandendday Get a specified date sit on the first day of the month and the last day
ALTER FUNCTION [dbo]. [Ufn_getmonthfirstandendday] (@tmpDate DATETIME)
RETURNS @tmpTable TABLE (
FirstDay DATETIME,
Endday DATETIME
)
As
BEGIN
INSERT into @tmpTable
SELECT A.firstday,b.endday from (
SELECT 1 as ID, CONVERT (varchar), DATEADD (Day,-datepart (Day,getdate ()) +1,getdate ()), + ' 00:00:00 ' as ' firstday '
) A
Left JOIN (
SELECT 1 as ID, CONVERT (varchar), DATEADD (Day,-1,dateadd (Month,1,getdate ()-day (GETDATE ()) +1), + ' 23:59:59 ' as ' Endday '
) b
On a.id = b.ID
RETURN
END
MSSQL gets the first day and the last day of the week on the specified date. Gets the specified date on the first and last days of the month