SQL 判断给定日期值(或时间段)所在星期的星期一和星期天的日期
程序员文章站
2023-12-15 12:57:46
一、给定一个日期值，求出此日期所在星期的星期一和星期天的日期数据 例如给定一个日期 2010-09-01，求出它所在星期的星期一是2010-08-30，星期天是2010-0...
一、给定一个日期值，求出此日期所在星期的星期一和星期天的日期数据
例如给定一个日期 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 <=@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
return
end
function测试:
select * from my_range_getweekfirstandenddays('2011-09-01','2011-10-06')
例如给定一个日期 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 <=@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
return
end
function测试:
复制代码 代码如下:
select * from my_range_getweekfirstandenddays('2011-09-01','2011-10-06')