To use a function for a recent report, you need to group the data source based on the week sequence of the occurrence date of the record. Therefore, two related sqlfunctions are written for calling.
To use a function for a recent report, you need to group the data source based on the week sequence of the occurrence date of the record. Therefore, two related SQL functions are written for calling.
1. Given a date value, obtain the date data of the Monday and Sunday of the week where the date is located.
For example, if you specify a date, find that its Monday of the week is, and Sunday is.
Function is created as follows:
The Code is as follows:
USE [MSSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo]. [My_OneDay_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
)
Left join (
SELECT 1 as id, DATEADD (wk, DATEDIFF (wk, 0, @ tmpDate), 6) AS EndDay
) B
ON a. ID = B. ID
RETURN
End
Function test:
The Code is as follows:
SELECT * from My_OneDay_GetWeekFirstAndEndDay ('2017-09-01 ')
2. Based on a single date search, the user enters two parameters: start date and end date. Based on these two parameters, calculate and sort the date tables of all weeks of Monday and Sunday in this period.
For example, if the start date is and the end date is, we can get the table for this week as follows:
WeekOrder FirstDay EndDay
1 00:00:00. 000 00:00:00. 000
2 00:00:00. 000 00:00:00. 000
3 00:00:00. 000 00:00:00. 000
4 00:00:00. 000 00:00:00. 000
5 00:00:00. 000 2011-10-02 00:00:00. 000
6 2011-10-03 00:00:00. 000 2011-10-09 00:00:00. 000
Function is created as follows:
The Code is as follows:
USE [MSSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo]. [MY_Range_GetWeekFirstAndEndDays] (@ tmpDateSTART DATETIME, @ tmpDateEND DATETIME)
RETURNS @ tmpTable TABLE (WeekOrder INT, FirstDay DATETIME, EndDay DATETIME)
AS
BEGIN
DECLARE @ tmpDate DATETIME
DECLARE @ index INT
SET @ tmpDate = @ tmpDateSTART
SET @ index = 1
WHILE @ tmpDate <= @ tmpDateEND
BEGIN
Insert into @ tmpTable
SELECT @ index, a. FirstDay, B. EndDay FROM (
SELECT 1 as id, DATEADD (wk, DATEDIFF (wk, 0, @ tmpDate), 0) AS FirstDAy)
Left join (
SELECT 1 as id, DATEADD (wk, DATEDIFF (wk, 0, @ tmpDate), 6) AS EndDay) B
ON a. ID = B. ID
SET @ tmpDate = DATEADD (DAY, 7, @ tmpDate)
SET @ index = @ index + 1
END
RETURN
End
Function test:
The Code is as follows:
SELECT * from My_Range_GetWeekFirstAndEndDays ('2017-09-01 ', '2017-10-06 ')
