return ( Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'29'-------- 如果你的@jieshou是三月的日期,這個很有可能報錯,如果是別的應該沒有問題。
create function f_getlasttenday1(@shuru char(8))returns datetime begin declare --@lasttedday smalldatetime, @jieshou datetime set @jieshou=dbo.chackdate(@shuru ) if datepart(dd,@jieshou)<11 if datepart(mm,@jieshou)=1 return dateadd(yy,-1,@jieshou)+'-'+'12'+'-'+'31' else begin if(datepart(mm,@jieshou) in ('2','4','6','8','9','11','1')) return datepart(yy,@jieshou)+'-'+dateadd(mm,-1,@jieshou)+'-'+'31' else begin if (datepart(mm,@jieshou)=3) begin if ((datepart(yy,@jieshou)%100>0) or datepart(yy,@jieshou)%400=0)
return datepart(yy,@jieshou)+'-'+dateadd(mm,-1,@jieshou)+'-'+'29' else return datepart(yy,@jieshou)+'-'+dateadd(mm,-1,@jieshou)+'-'+'28' end else
return datepart(yy,@jieshou)+'-'+dateadd(mm,-1,@jieshou)+'-'+'30' end end else begin if datepart(dd,@jieshou)<21 return datepart(yy,@jieshou)+'-'+datepart(mm,@jieshou)+'-'+'10' else return datepart(yy,@jieshou)+'-'+datepart(mm,@jieshou)+'-'+'20' end return '1' end
datepart(yy,@jieshou) = year(@jieshou)另外,這個得到的是Int型數據,和'-'這個字符組合的時候,需要將類型轉為varchar,所以好我加了Cast做轉換,所以就是Cast(year(@jieshou) As Varchar)。
--直接在你函數上修改create function f_getlasttenday1(@shuru char(8))returns datetime begin declare --@lasttedday smalldatetime, @jieshou datetime set @jieshou=dbo.chackdate(@shuru ) if datepart(dd,@jieshou)<11 if datepart(mm,@jieshou)=1 return Cast(year(@jieshou) -1 As Varchar)+'-'+'12'+'-'+'31' else begin if(datepart(mm,@jieshou) in ('2','4','6','8','9','11','1')) return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar)+'-'+'31' else begin if (datepart(mm,@jieshou)=3) begin if ((datepart(yy,@jieshou)%100=0) or datepart(yy,@jieshou)%400=0) return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'29' else return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'28' end else
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'30' end end else begin if datepart(dd,@jieshou)<21 return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar) +'-'+'10' else return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar) +'-'+'20' end return '1' end GO Select dbo.f_getlasttenday1('20070130') Select dbo.f_getlasttenday1('20070330') Select dbo.f_getlasttenday1('20070310') GO Drop Function f_getlasttenday1
if ((datepart(yy,@jieshou)%100>0) or datepart(yy,@jieshou)%400=0)------這個地方寫的有問題
那就是因为 if ((datepart(yy,@jieshou)%100>0) or datepart(yy,@jieshou)%400=0) 这条有问题是吧 谢谢鱼
--將你的那個函數屏蔽掉,直接set @jieshou= Cast(@shuru As DateTime)這麼寫,以下代碼是運行OK的。create function f_getlasttenday1(@shuru char(8))returns datetime begin declare --@lasttedday smalldatetime, @jieshou datetime set @jieshou= Cast(@shuru As DateTime) --set @jieshou=dbo.chackdate(@shuru ) if datepart(dd,@jieshou)<11 if datepart(mm,@jieshou)=1 return Cast(year(@jieshou) -1 As Varchar)+'-'+'12'+'-'+'31' else begin if(datepart(mm,@jieshou) in ('2','4','6','8','9','11','1')) return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar)+'-'+'31' else begin if (datepart(mm,@jieshou)=3) begin if ((datepart(yy,@jieshou)%100=0) or datepart(yy,@jieshou)%400=0) return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'29' else return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'28' end else
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'30' end end else begin if datepart(dd,@jieshou)<21 return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar) +'-'+'10' else return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar) +'-'+'20' end return '1' end GO Select dbo.f_getlasttenday1('20070130') Select dbo.f_getlasttenday1('20070330') Select dbo.f_getlasttenday1('20070310') GO Drop Function f_getlasttenday1
Select @jieshou = GetDate()
Select dateadd(mm,-1,@jieshou)
--Result
/*
2007-03-11 17:11:12.387*/
这样写可以吗?
返回的是smalldatetime类型
这样写可以吗?-------
改為
return ( Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'29'
这是怎么回事啊?
不行吗?
如果你的@jieshou是三月的日期,這個很有可能報錯,如果是別的應該沒有問題。
begin
declare --@lasttedday smalldatetime,
@jieshou datetime
set @jieshou=dbo.chackdate(@shuru )
if datepart(dd,@jieshou)<11
if datepart(mm,@jieshou)=1
return dateadd(yy,-1,@jieshou)+'-'+'12'+'-'+'31'
else
begin
if(datepart(mm,@jieshou) in ('2','4','6','8','9','11','1'))
return datepart(yy,@jieshou)+'-'+dateadd(mm,-1,@jieshou)+'-'+'31'
else
begin
if (datepart(mm,@jieshou)=3)
begin
if ((datepart(yy,@jieshou)%100>0) or datepart(yy,@jieshou)%400=0)
return datepart(yy,@jieshou)+'-'+dateadd(mm,-1,@jieshou)+'-'+'29'
else
return datepart(yy,@jieshou)+'-'+dateadd(mm,-1,@jieshou)+'-'+'28'
end
else
return datepart(yy,@jieshou)+'-'+dateadd(mm,-1,@jieshou)+'-'+'30'
end
end
else
begin
if datepart(dd,@jieshou)<21
return datepart(yy,@jieshou)+'-'+datepart(mm,@jieshou)+'-'+'10'
else
return datepart(yy,@jieshou)+'-'+datepart(mm,@jieshou)+'-'+'20'
end
return '1'
end
错误都是
从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界。
chackdate的作用是什麼?
begin
declare --@lasttedday smalldatetime,
@jieshou datetime
set @jieshou=dbo.chackdate(@shuru )
if datepart(dd,@jieshou)<11
if datepart(mm,@jieshou)=1
return Cast(year(@jieshou) -1 As Varchar)+'-'+'12'+'-'+'31'
else
begin
if(datepart(mm,@jieshou) in ('2','4','6','8','9','11','1'))
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar)+'-'+'31'
else
begin
if (datepart(mm,@jieshou)=3)
begin
if ((datepart(yy,@jieshou)%100=0) or datepart(yy,@jieshou)%400=0)
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'29'
else
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'28'
end
else
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'30'
end
end
else
begin
if datepart(dd,@jieshou)<21
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar) +'-'+'10'
else
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar) +'-'+'20'
end
return '1'
end
GO
Select dbo.f_getlasttenday1('20070130')
Select dbo.f_getlasttenday1('20070330')
Select dbo.f_getlasttenday1('20070310')
GO
Drop Function f_getlasttenday1
这条出现了越界错误。
if ((datepart(yy,@jieshou)%100>0) or datepart(yy,@jieshou)%400=0)
这条有问题是吧
谢谢鱼
begin
declare --@lasttedday smalldatetime,
@jieshou datetime
set @jieshou= Cast(@shuru As DateTime)
--set @jieshou=dbo.chackdate(@shuru )
if datepart(dd,@jieshou)<11
if datepart(mm,@jieshou)=1
return Cast(year(@jieshou) -1 As Varchar)+'-'+'12'+'-'+'31'
else
begin
if(datepart(mm,@jieshou) in ('2','4','6','8','9','11','1'))
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar)+'-'+'31'
else
begin
if (datepart(mm,@jieshou)=3)
begin
if ((datepart(yy,@jieshou)%100=0) or datepart(yy,@jieshou)%400=0)
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'29'
else
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'28'
end
else
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) - 1 As Varchar) +'-'+'30'
end
end
else
begin
if datepart(dd,@jieshou)<21
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar) +'-'+'10'
else
return Cast(year(@jieshou) As Varchar)+'-'+ Cast(month(@jieshou) As Varchar) +'-'+'20'
end
return '1'
end
GO
Select dbo.f_getlasttenday1('20070130')
Select dbo.f_getlasttenday1('20070330')
Select dbo.f_getlasttenday1('20070310')
GO
Drop Function f_getlasttenday1