澳门新葡萄京官网首页 3

澳门新葡萄京官网首页sql获取指定时间段数据的语句

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

参考此帖: 如果您无法打开此链接,也可以下载此论题图。

Function创建如下:

根据出生日期计算出在某个事件日期当时的年龄。因为Insus.NET把它写成一个自定义函数UDF_Age:

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测试:

澳门新葡萄京官网首页 1澳门新葡萄京官网首页 2View Code

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

二、以上面单个日期搜索为基础,由用户输入两个参数,一个是开始日期,一个结束日期,根据这两个参数,求出在此时期段内的所有星期的星期一和星期天的日期表并排序。例如开始日期是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

IF OBJECT_ID(‘[dbo].[udf_Age]’) IS NOT NULL 
DROP FUNCTION [dbo].[udf_Age]
GO

代码如下复制代码

CREATE FUNCTION [dbo].[udf_Age](
    @BirthDate DATETIME,
    @EventDate DATETIME
)
RETURNS INT
AS
BEGIN
    –没有超过一岁的,均为0岁
    RETURN DATEDIFF(YEAR, @BirthDate,@EventDate) – CASE WHEN 100 * MONTH(@EventDate) + DAY(@EventDate) < 100 * MONTH(@BirthDate) + DAY(@BirthDate)
    THEN 1 ELSE 0 END
END

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 [dbo].[udf_Age](‘1988-08-06′,’2011-04-18’)

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

 

结果:

 澳门新葡萄京官网首页 3

发表评论

电子邮件地址不会被公开。 必填项已用*标注