定义一个标量值函数dbo.fun1(@a varchar(30)), 返回datetime类型的值。执行‘select dbo.fun1(columnA) from tableA’(其中columnA字段类型为datetime类型),就会出错‘无法将字符串类型转化为datetime类型’。请各位大侠指点下,这种情况在其他机子是可以的,只有我的机子不行。非常感谢!
调试欢乐多
函数具体如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[usf_format_date_to_glb_locale]
(
@in_vchUserLocaleID VARCHAR(10),
@in_vchDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE
-- Local Variables @v_dtFormat NVARCHAR(30),
@v_global_dt_format INT,
@v_user_lc_dt_format INT,
@v_date DATETIME,
@v_nErrorNumber INT
SELECT @v_global_dt_format = date_format
FROM t_locale_date_format
WHERE locale_id IN
(SELECT c1
FROM t_control
WHERE RTRIM(LTRIM(control_type)) = 'GLOBAL_DB_LOCALE') SELECT @v_nErrorNumber = @@ERROR
IF @v_nErrorNumber <> 0 OR LEN(RTRIM(LTRIM(@v_global_dt_format)))=0
BEGIN
SET @v_global_dt_format = '101'
END SET @v_global_dt_format = ISNULL(@v_global_dt_format,101) SELECT @v_user_lc_dt_format = CONVERT(INTEGER, date_format)
FROM t_locale_date_format
WHERE locale_id = @in_vchUserLocaleID SELECT @v_date = CONVERT(DATETIME, @in_vchDate, @v_user_lc_dt_format)
-----------------------------------------------------------------------------------
-- Exit the Process
-----------------------------------------------------------------------------------
EXIT_LABEL: -- Always leave from here.
RETURN @v_date
END
函数如下:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[usf_format_date_to_glb_locale]
(
@in_vchUserLocaleID VARCHAR(10),
@in_vchDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE
-- Local Variables @v_dtFormat NVARCHAR(30),
@v_global_dt_format INT,
@v_user_lc_dt_format INT,
@v_date DATETIME,
@v_nErrorNumber INT
SELECT @v_global_dt_format = date_format
FROM t_locale_date_format
WHERE locale_id IN
(SELECT c1
FROM t_control
WHERE RTRIM(LTRIM(control_type)) = 'GLOBAL_DB_LOCALE') SELECT @v_nErrorNumber = @@ERROR
IF @v_nErrorNumber <> 0 OR LEN(RTRIM(LTRIM(@v_global_dt_format)))=0
BEGIN
SET @v_global_dt_format = '101'
END SET @v_global_dt_format = ISNULL(@v_global_dt_format,101) SELECT @v_user_lc_dt_format = CONVERT(INTEGER, date_format)
FROM t_locale_date_format
WHERE locale_id = @in_vchUserLocaleID SELECT @v_date = CONVERT(DATETIME, @in_vchDate, @v_user_lc_dt_format)
-----------------------------------------------------------------------------------
-- Exit the Process
-----------------------------------------------------------------------------------
EXIT_LABEL: -- Always leave from here.
RETURN @v_date
END
这样试试 @v_dtFormat NVARCHAR(30),
@v_global_dt_format INT,
@v_user_lc_dt_format INT,
@v_date DATETIME,--改为 @v_date (varchar(20),
@v_nErrorNumber INT
SELECT @v_date = CONVERT(DATETIME, @in_vchDate, @v_user_lc_dt_format)
--改为 SELECT @v_date = CONVERT(varchar(20), @in_vchDate, @v_user_lc_dt_format)
--经简单测试,没有发现任何问题,检查下你表't_locale_date_format'和't_control'中的数据,可能和这两个表的数据有关
set nocount on
if object_id('t_locale_date_format') is not null drop table t_locale_date_format
go
if object_id('t_control') is not null drop table t_control
go
if object_id('tableA') is not null drop table tableA
go
create table t_locale_date_format(
locale_id int ,
date_format int
)
create table t_control(
c1 int ,
control_type varchar(200)
)
create table tableA(
columnA datetime
)
go
insert into t_locale_date_format values(1,101)
insert into t_control values(1,'GLOBAL_DB_LOCALE')
insert into tableA values(getdate())
insert into tableA values(null)
go
select dbo.[usf_format_date_to_glb_locale](1,'2011-11-25 12:26:37.370') 直接转换
go
select dbo.[usf_format_date_to_glb_locale](1,columnA) 表中转换 from tableA
go
/*
直接转换
-----------------------
2011-11-25 12:26:37.370表中转换
-----------------------
2012-09-01 21:59:17.153
NULL*/
SELECT @v_global_dt_format = date_format--此值为101
FROM t_locale_date_format
WHERE locale_id IN
(SELECT c1
FROM t_control
WHERE RTRIM(LTRIM(control_type)) = 'GLOBAL_DB_LOCALE')SELECT @v_user_lc_dt_format = CONVERT(INTEGER, date_format)--此值为101
FROM t_locale_date_format
WHERE locale_id = @in_vchUserLocaleID