sql获取指定时间段数据的语句

例如给定一个日期
2010-09-01,求出它所在星期的星期一是2010-08-30,星期天是2010-09-05
代码如下复制代码

Function创建如下:

USE [MSSQL]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE
FUNCTION [dbo].[My_OneDay_GetWeekFirstAndEndDay](@tmpDate
DATETIME)RETURNS @tmpTable TABLE(FirstDay DATETIME , EndDay
DATETIME)ASBEGIN 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 RETURNENDFunction测试:

SELECT * from My_OneDay_GetWeekFirstAndEndDay(‘2010-09-01’)

二、以上面单个日期搜索为基础,由用户输入两个参数,一个是开始日期,一个结束日期,根据这两个参数,求出在此时期段内的所有星期的星期一和星期天的日期表并排序。例如开始日期是2011-09-01,结束日期是2011-10-06,我们就可以得到此星期表如下:WeekOrder
FirstDay EndDay1 2011-08-29 00:00:00.000 2011-09-04 00:00:00.0002
2011-09-05 00:00:00.000 2011-09-11 00:00:00.0003 2011-09-12 00:00:00.000
2011-09-18 00:00:00.0004 2011-09-19 00:00:00.000 2011-09-25
00:00:00.0005 2011-09-26 00:00:00.000 2011-10-02 00:00:00.0006
2011-10-03 00:00:00.000 2011-10-09 00:00:00.000

代码如下复制代码

Function创建如下:

USE [MSSQL]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE
FUNCTION [dbo].[MY_Range_澳门新葡亰3522平台游戏,GetWeekFirstAndEndDays](@tmpDateSTART
DATETIME,@tmpDateEND DATETIME)RETURNS @tmpTable TABLE(WeekOrder
INT,FirstDay DATETIME , EndDay DATETIME)ASBEGIN DECLARE @tmpDate
DATETIMEDECLARE @index INTSET @tmpDate=@tmpDateSTARTSET @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) a 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
RETURNENDFunction测试:

SELECT * from
My_Range_GetWeekFirstAndEndDays(‘2011-09-01′,’2011-10-06’)