How to judge a given date value in SQL
In this issue, the editor will bring you about how to judge a given date value in SQL. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.
1. Given a date value, find the date data of Monday and Sunday of the week in which the date is located.
For example, given a date 2010-09-01, find out that the Monday of the week is 2010-08-30 and Sunday is 2010-09-05.
The Function is created 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.FirstDayreb.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
Function Test:
SELECT * from My_OneDay_GetWeekFirstAndEndDay ('2010-09-01')
Second, based on the above single date search, the user inputs two parameters, one is the start date and the other is the end date. According to these two parameters, the date table of Mondays and Sundays of all weeks in this period is obtained and sorted.
For example, if the start date is 2011-09-01 and the end date is 2011-10-06, we can get the following week table:
WeekOrder FirstDay EndDay
1 2011-08-29 00 00.000 2011-09-04 00 00
2 2011-09-05 2011-09-11 00.000 2011-09-11 00000
3 2011-09-12 0000RV 00.000 2011-09-18 0000RV 00.000
4 2011-09-19 0000Rose 00.000 2011-09-250000Rose 00.000
5 2011-09-26 0000Rose 00.000 2011-10-0200VOO 00.000
6 2011-10-03 00 00.000 2011-10-09 00 00
The Function is created 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
