search

Home  >  Article  >  Database  >  sql获取指定时间段数据的语句

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

WBOY
WBOYOriginal
2016-06-07 17:48:351746browse

例如给定一个日期 2010-09-01，求出它所在星期的星期一是2010-08-30，星期天是2010-09-05

 代码如下 复制代码

 Function创建如下:

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.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
    RETURN
END
Function测试:

SELECT * from  My_OneDay_GetWeekFirstAndEndDay('2010-09-01')

 

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

 代码如下 复制代码

 Function创建如下:

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         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
     RETURN
END
Function测试:

SELECT * from  My_Range_GetWeekFirstAndEndDays('2011-09-01','2011-10-06')
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：sql触发器详解Next article：SQL保留指定位数的小数位

Related articles

See more