select convert(varchar(6) , 日期列 , 112) from tb where convert(varchar(6) , 日期列 , 112) between '200701' and '200705'
select convert(varchar(6) , 日期列 , 112) from tb where convert(varchar(6) , 日期列 , 112) between '200701' and '200705'
from tb 这个数据表是什么?
select convert(varchar(6) , 日期列 , 112) from 你的表名 where convert(varchar(6) , 日期列 , 112) between '200701' and '200705'
我的这个查询没有数据表 就是通过输入的两个参数 计算月份 WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1), L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B), L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B), L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B), L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B), L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B), Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5) SELECT substring(convert(varchar(8),dateadd(month,n-1,CONVERT(datetime , @Date1 )),112),1,6 ) as DATE FROM Nums WHERE n <= datediff(month,CONVERT( datetime , @Date1 ),CONVERT( datetime ,@Date2 ))+1 在计算的时候会保存
为什么要整这么复杂 不是有datediff()日期时间函数吗?
我的这个查询没有数据表 就是通过输入的两个参数 计算月份 WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1), L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B), L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B), L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B), L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B), L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B), Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5) SELECT substring(convert(varchar(8),dateadd(month,n-1,CONVERT(datetime , @Date1 )),112),1,6 ) as DATE FROM Nums WHERE n <= datediff(month,CONVERT( datetime , @Date1 ),CONVERT( datetime ,@Date2 ))+1 在在asp.net 中会计算的时候会出错!
create table tb(dt varchar(6)) gocreate procedure my_proc @dt1 as varchar(6),@dt2 as varchar(6) as begin declare @dt3 as varchar(6) set @dt3 = @dt1 while @dt3 <= @dt2 begin insert into tb values(@dt3) set @dt3 = convert(varchar(6),dateadd(mm , 1 , left(@dt3,4)+'-'+right(@dt3,2) + '-01'),112) end end goexec my_proc '200701' , '200705' select * from tbdrop table tb drop procedure my_proc/* dt ------ 200701 200702 200703 200704 200705(所影响的行数为 5 行) */
declare @Bday varchar(10) , @Eday varchar(10) , @Pday datetime , @i int , @j intset @bday='200801' set @eday='200803' set @i=0 set @j=1select @j= datediff(mm,convert(datetime,@bday+'01' ),convert(datetime,@eday+'01' )) while @i<@j+1 begin select @pday=dateadd(mm,@i,convert(datetime,@bday+'01')) print left(convert(varchar(10),@pday,112),6) set @i=@i+1 --print @i end
xikboy 狼面书生 你的是Print 的没法读出!
declare @t table(date varchar(10)) insert @t select '200701'union all select '200702'union all select '200703'union all select '200704'union all select '200705' select date from @t where date between '200701' and '200704'
where convert(varchar(6) , 日期列 , 112) between '200701' and '200705'
where convert(varchar(6) , 日期列 , 112) between '200701' and '200705'
这个数据表是什么?
where convert(varchar(6) , 日期列 , 112) between '200701' and '200705'
就是通过输入的两个参数
计算月份
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT substring(convert(varchar(8),dateadd(month,n-1,CONVERT(datetime , @Date1 )),112),1,6 ) as DATE FROM Nums WHERE n <= datediff(month,CONVERT( datetime , @Date1 ),CONVERT( datetime ,@Date2 ))+1
在计算的时候会保存
就是通过输入的两个参数
计算月份 WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT substring(convert(varchar(8),dateadd(month,n-1,CONVERT(datetime , @Date1 )),112),1,6 ) as DATE FROM Nums WHERE n <= datediff(month,CONVERT( datetime , @Date1 ),CONVERT( datetime ,@Date2 ))+1 在在asp.net 中会计算的时候会出错!
gocreate procedure my_proc @dt1 as varchar(6),@dt2 as varchar(6)
as
begin
declare @dt3 as varchar(6)
set @dt3 = @dt1
while @dt3 <= @dt2
begin
insert into tb values(@dt3)
set @dt3 = convert(varchar(6),dateadd(mm , 1 , left(@dt3,4)+'-'+right(@dt3,2) + '-01'),112)
end
end
goexec my_proc '200701' , '200705'
select * from tbdrop table tb
drop procedure my_proc/*
dt
------
200701
200702
200703
200704
200705(所影响的行数为 5 行)
*/
SET @dt=GETDATE()DECLARE @number int
SET @number=3--1.指定日期该年的第一天或最后一天
--A. 年的第一天
SELECT CONVERT(char(5),@dt,120)+'1-1'--B. 年的最后一天
SELECT CONVERT(char(5),@dt,120)+'12-31'
--2.指定日期所在季度的第一天或最后一天
--A. 季度的第一天
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt)-2,
@dt),
120)+'1')--B. 季度的最后一天(CASE判断法)
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)
+CASE WHEN DATEPART(Quarter,@dt) in(1,4)
THEN '31'ELSE '30' END)--C. 季度的最后一天(直接推算法)
SELECT DATEADD(Day,-1,
CONVERT(char(8),
DATEADD(Month,
1+DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)+'1')
--3.指定日期所在月份的第一天或最后一天
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')--C. 月的最后一天(容易使用的错误方法)
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))
--4.指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
--5.指定日期所在周的任意星期几
--A. 星期天做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)--B. 星期一做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=2203195
, @Eday varchar(10)
, @Pday datetime
, @i int
, @j intset @bday='200801'
set @eday='200803'
set @i=0
set @j=1select @j= datediff(mm,convert(datetime,@bday+'01' ),convert(datetime,@eday+'01' ))
while @i<@j+1
begin
select @pday=dateadd(mm,@i,convert(datetime,@bday+'01'))
print left(convert(varchar(10),@pday,112),6)
set @i=@i+1
--print @i
end
狼面书生
你的是Print 的没法读出!
insert @t select '200701'union all
select '200702'union all
select '200703'union all
select '200704'union all
select '200705'
select date from @t where date between '200701' and '200704'