我想知道这样的效果
比如我传两个日期 2009-05-04 ,2009-08-08,
我想得到的这两个日期之内的所有的月初和月末例如这个就像得到2009-05-01,2009-05-31,2009-06-01,2009-06-30,2009-08-01,2009-08-31大侠帮帮忙 谢谢
比如我传两个日期 2009-05-04 ,2009-08-08,
我想得到的这两个日期之内的所有的月初和月末例如这个就像得到2009-05-01,2009-05-31,2009-06-01,2009-06-30,2009-08-01,2009-08-31大侠帮帮忙 谢谢
解决方案 »
- where ... and ...的执行顺序
- 问个很弱的小问题,马上结贴?........问个很弱的小问题,马上结贴?
- 执行存储过程时,存储过程里的事务回滚了能抛出异常吗?
- 急:数据库还原问题
- 帮忙解决一条查询语句!急~~~~
- 求一Sql语句 能够搜索出含有 "[" 和 "]" 语句 比如能够搜索出 [4726-90] 或 [M385-092] 等
- 关于用order by排序的问题,请大家一起看看
- 跨服务器查询(在线等……)
- 错误: 823,严重度: 24,状态: 2 问题
- $$$$$$$$如何得到局域网中所有的SQL实例的例表$$$$$$$$$$
- 简单SQL语法
- 如何用SQL语句在字符串中插入一个字符
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')
declare @a datetime
declare @b datetime
declare @i int
set @a = convert(datetime,'2009-05-04')
set @b = convert(datetime,'2009-08-08')
declare @t table( dt datetime)
while (datepart(y,convert(datetime,@a)) <= datepart(y,convert(datetime,@b)))
AND (datepart(m,convert(datetime,@a)) <= datepart(m,convert(datetime,@b)))
begin
insert into @t
SELECT CONVERT(datetime,CONVERT(char(8),@a,120)+'1')
insert into @t
select DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@a),120)+'1')
set @a = dateadd(Month,1, @a)
end
select CONVERT(char(10), dt, 120) from @t
set @s='2009-05-04'
set @e='2009-08-08'
SELECT
月初=case when CONVERT(datetime,CONVERT(char(8),dateadd(month,number,@s),120)+'1') between @s and @e
then CONVERT(datetime,CONVERT(char(8),dateadd(month,number,@s),120)+'1')
else null
end,
月末=case when DATEADD(Day,-1,CONVERT(char(8),DATEADD(month,number+1,@s),120)+'1') between @s and @e
then DATEADD(Day,-1,CONVERT(char(8),DATEADD(month,number+1,@s),120)+'1')
else null
end
from
master..spt_values
where
type='p' and number>=0 and datediff(month,@s,@e)>=number
月初 月末
----------------------- -----------------------
NULL 2009-05-31 00:00:00.000
2009-06-01 00:00:00.000 2009-06-30 00:00:00.000
2009-07-01 00:00:00.000 2009-07-31 00:00:00.000
2009-08-01 00:00:00.000 NULL(4 行受影响)
declare @sdate datetime
declare @edate datetime
set @sdate = '2009-05-04'
set @edate = '2009-08-08'
select
cast(left(convert(varchar(10),dateadd(mm,num,@sdate),120) ,8) + '01' as datetime) 月初,
cast(left(convert(varchar(10),dateadd(mm,num+1,@sdate),120) ,8) + '01' as datetime) - 1 月末
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(mm,num,@sdate)<=@edate/*
月初 月末
------------------------------------------------------ ------------------------------------------------------
2009-05-01 00:00:00.000 2009-05-31 00:00:00.000
2009-06-01 00:00:00.000 2009-06-30 00:00:00.000
2009-07-01 00:00:00.000 2009-07-31 00:00:00.000
2009-08-01 00:00:00.000 2009-08-31 00:00:00.000(所影响的行数为 4 行)*/