电脑安全

您当前的位置:主页 > 电脑安全 >

sql获取指定时间段数据的语句-网络编程

来源:[db:来源]标题:   日期    编辑:电脑技术全能网 时间:2019-09-20 18:31

原标题: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') 2、以下面单个日期搜寻为基本,由用户输出两个参数,一个是开端日期,一个停止日期,依据这两个参数,求出在此时代段内的全部礼拜的礼拜一和礼拜天的日期表并排序。比方开端日期是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_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')

上一篇:一段asp转php-网络编程

下一篇:没有了