本来想实现一个简单的功能:传入一个字符串,然后用Convert(datetime,'')来判断会不会出错,如果出错返回false,如果成功返回true。但函数内不能使用try...catch...。如下写,又会直接报错,求高人指点。跪谢。
-- Description: <判断字符串是否可以转换成日期>
-- =============================================
create FUNCTION [dbo].[GetDateOr2]
(
@VarDate varchar(100)
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @ReDate datetime
DECLARE @ResultVar varchar(100)
set @ReDate = convert(datetime,@VarDate);
IF @@ERROR <> 0
set @ResultVar = '1' --成功
ELSE
set @ResultVar = '0' --失败
RETURN @ResultVar
END
-- Description: <判断字符串是否可以转换成日期>
-- =============================================
create FUNCTION [dbo].[GetDateOr2]
(
@VarDate varchar(100)
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @ReDate datetime
DECLARE @ResultVar varchar(100)
set @ReDate = convert(datetime,@VarDate);
IF @@ERROR <> 0
set @ResultVar = '1' --成功
ELSE
set @ResultVar = '0' --失败
RETURN @ResultVar
END
08 27 2010 3:03AM 2010-07-24 16:26:14.847
2009-01-07 09:00 )还有未知的类型,在convert的时候出错。大哥,小弟也是广州的
go
Create function GetDateOr2(
@VarDate varchar(100)
)
returns bit
as
begin
declare @i int
if isdate(@VarDate)=1
return 1
if @VarDate not like '%[AP]M'
return 0
set @i=charindex(' ',reverse(@VarDate))
if isdate(right(@VarDate,@i-1))=1
return( isdate(replace(left(@VarDate,len(@VarDate)-@i),' ','-')))
return 0
end
go--> --> (Roy)生成測試數據
declare @T table([Col1] nvarchar(200))
Insert @T
select N'2010-9-29' union all
select N'2008-11-05' union all
select N'2008-12-10 00:00:00' union all
select N'08 27 2010 3:03AM' union all
select N'2010-07-24 16:26:14.847' union all
select N'2009-01-07 09:00'
select *,dbo.GetDateOr2([Col1]) as [IsDate] from @T/*
Col1 IsDate
2010-9-29 1
2008-11-05 1
2008-12-10 00:00:00 1
08 27 2010 3:03AM 1
2010-07-24 16:26:14.847 1
2009-01-07 09:00 1
*/给楼主写一个例子