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

Today, I will talk to you about how to judge a given date value in SQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1. Given a date value, find out the date data of Monday and Sunday of the week on which the date is located. For example, given a date of 2010-09-01, the Monday of the week is 2010-08-30. Sunday is 2010-09-05 Function created as follows: copy the code 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.FirstDayJournal 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

Function test: copy the code as follows: 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, the start date is 2011-09-01 and the end date is 2011-10-06 We can get this week's table as follows: WeekOrder FirstDay EndDay 1 2011-08-29 00 here is 00.000 2011-09-04 0000 JV 00.000 2 2011-09-05 0000 JV 00.000 2011-09-11 0000 Flex 00.000 3 2011-09-12 0000 JV 00.000 2011-09-18 0000 Junction 00.000 4 2011-09-19 0000 Junction 00.000 2011-09-25 00001-09-25 00JV 00.000 2011-09-5 2011-11 09-26 00 MSSQL 00 Function 00.000 2011-10-02 00 Function 00.000 6 2011-10-03 00 Function create as follows: copy code: 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.