Shulou(Shulou.com)05/31 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.