浏览 1914 次
锁定老帖子 主题:数据库自定义方法及在Sql语句中调用
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2012-04-17
--Sql中自定义方法 create function StrToDateTime(@DateStr as varchar(18)) returns varchar(24) --注意 returns 不是 return as begin declare @date varchar(24); set @date=@DateStr; set @date=(SUBSTRING(cast (@date as varchar(18)),1,4)+'-'+SUBSTRING(cast (@date as varchar(18)),5,2) +'-'+SUBSTRING(cast (@date as varchar(18)),7,2) +' '+SUBSTRING(cast (@date as varchar(18)),9,2) +':'+SUBSTRING(cast (@date as varchar(18)),11,2) +':'+SUBSTRING(cast (@date as varchar(18)),13,2)); return @date; end declare @returntime varchar(24) exec @returntime=StrToDateTime '20120401145112827' print convert(varchar(24),convert(datetime,@returntime),20) --初始Sql语句 未使用自定义方法 select * from ( select id,interID,cpid,intername,reply,lasttime,CASE lasttime WHEN '0' THEN getdate()-3 Else cast((SUBSTRING(cast (lasttime as varchar(18)),1,4) +'-'+SUBSTRING(cast (lasttime as varchar(18)),5,2) +'-'+SUBSTRING(cast (lasttime as varchar(18)),7,2) +' '+SUBSTRING(cast (lasttime as varchar(18)),9,2) +':'+SUBSTRING(cast (lasttime as varchar(18)),11,2) +':'+SUBSTRING(cast (lasttime as varchar(18)),13,2) ) as varchar(24)) end as ldate,setTime from xsreuserinter ) as temp where (datediff(hh,temp.ldate,getdate())>72 and datediff(hh,temp.setTime,getdate())>72) order by lasttime desc --调用该方法之后的Sql语句 --Notice: 调用该方法时 一定要加前缀dbo. 否则 报错“该内置方法不识别” 该方法在哪个数据库下执行 只属于哪个数据库 跨数据库无效 --调用方法为 select dbo.StrToDateTime(lasttime) select * from ( select id,interID,cpid,intername,reply,lasttime,CASE lasttime WHEN '0' THEN getdate()-3 Else convert(datetime,dbo.StrToDateTime(lasttime),20) --该处调用function end as ldate,setTime from xsreuserinter ) as temp where (datediff(hh,temp.ldate,getdate())>72 and datediff(hh,temp.setTime,getdate())>72) order by lasttime desc 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |