为什么直接写不行:从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界。 declare @d1 datetime set @d1='2004-5-11'select * from db where InDATE <=@d1 and InDate >= convert(char(10),(left(@d1,4)+'-1-1'),120)
呵呵,好久没写sql,看看这么写行不行?(我手边没环境测试)creat procedure aaa(declare @t0 datetime) as declare @t1 char(10) declare @t2 char(10) set @t1 = convert(char(10),@t0,120) set @t2 = left(@t1,4) + '-01-01'select * from tablename where convert(char(10),built_date,120) between @t1 and @t2go
SprinBell() ,你的方法试试这么写怎么样? declare @d1 datetime set @d1='2004-05-11'select * from db where InDATE <=@d1 and InDate >= convert(datetime,convert(char(4)@d1,120)+'-01-01'),120)这样应该没问题啦,你的InDATE我想应该是datetime类型,“convert(char(10),(left(@d1,4)+'-1-1'),120)”应该产生类型为char(10)的值,所以这样转化就不对;导致 datetime 值越界是因为你的(left(@d1,4)+'-1-1'),是因为left(@d1,4)产生的值为'05 '而不是你想要的‘2004’,导致(left(@d1,4)+'-1-1'),的结果变成了'05 -1-1',从而造成datetime 值越界。差不多就是这样吧,呵呵~~
不好意思,应该是 declare @d1 datetime set @d1='2004-05-11'select * from db where InDATE <=@d1 and InDate >= convert(datetime,convert(char(4),@d1,120)+'-01-01',120),120)刚才漏打了一个逗号和一个条件120
declare @ datetime set @ = getdate()select dateadd(year,datediff(year,0,@),0)
declare @ datetime set @ = getdate()select dateadd(year,datediff(year,0,@),0) as 所在年的第一天 ,dateadd(year,1+datediff(year,0,@),0)-1 as 所在年的最后一天 ,dateadd(quarter,datediff(quarter,0,@),0) as 所在季的第一天 ,dateadd(quarter,1+datediff(quarter,0,@),0)-1 as 所在季的最后一天 ,dateadd(month,datediff(month,0,@),0) as 所在月的第一天 ,dateadd(month,1+datediff(month,0,@),0)-1 as 所在月的最后一天 ,dateadd(week,datediff(week,0,@),0) as 所在周的第一天 ,dateadd(week,1+datediff(week,0,@),0)-1 as 所在周的最后一天
set @d1='2004-5-11'select @d2=datename(year,@d1)+'-1-1'select 结果=@d2/*--测试结果结果
------------------------------------------------------
2004-01-01 00:00:00.000(所影响的行数为 1 行)
--*/
declare @d1 datetime
set @d1='2004-5-11'select * from db where InDATE <=@d1 and InDate >= convert(char(10),(left(@d1,4)+'-1-1'),120)
呵呵,好久没写sql,看看这么写行不行?(我手边没环境测试)creat procedure aaa(declare @t0 datetime) as declare @t1 char(10)
declare @t2 char(10)
set @t1 = convert(char(10),@t0,120)
set @t2 = left(@t1,4) + '-01-01'select * from tablename where convert(char(10),built_date,120) between @t1 and @t2go
declare @d1 datetime
set @d1='2004-05-11'select * from db where InDATE <=@d1 and InDate >= convert(datetime,convert(char(4)@d1,120)+'-01-01'),120)这样应该没问题啦,你的InDATE我想应该是datetime类型,“convert(char(10),(left(@d1,4)+'-1-1'),120)”应该产生类型为char(10)的值,所以这样转化就不对;导致 datetime 值越界是因为你的(left(@d1,4)+'-1-1'),是因为left(@d1,4)产生的值为'05 '而不是你想要的‘2004’,导致(left(@d1,4)+'-1-1'),的结果变成了'05 -1-1',从而造成datetime 值越界。差不多就是这样吧,呵呵~~
declare @d1 datetime
set @d1='2004-05-11'select * from db where InDATE <=@d1 and InDate >= convert(datetime,convert(char(4),@d1,120)+'-01-01',120),120)刚才漏打了一个逗号和一个条件120
set @ = getdate()select dateadd(year,datediff(year,0,@),0)
set @ = getdate()select dateadd(year,datediff(year,0,@),0) as 所在年的第一天
,dateadd(year,1+datediff(year,0,@),0)-1 as 所在年的最后一天
,dateadd(quarter,datediff(quarter,0,@),0) as 所在季的第一天
,dateadd(quarter,1+datediff(quarter,0,@),0)-1 as 所在季的最后一天
,dateadd(month,datediff(month,0,@),0) as 所在月的第一天
,dateadd(month,1+datediff(month,0,@),0)-1 as 所在月的最后一天
,dateadd(week,datediff(week,0,@),0) as 所在周的第一天
,dateadd(week,1+datediff(week,0,@),0)-1 as 所在周的最后一天