引入一个临时表,就会变得很简单了。。 SELECT CAST(CAST(CAST(V2_T22_VIS1YY AS INT) AS VARCHAR) +'-'+CAST(CAST(V2_T22_VIS1MM AS INT) AS VARCHAR)+'-'+CAST(CAST(V2_T22_VIS1DD AS INT) AS VARCHAR)AS DATETIME) AS V2_VIS1DATE, CAST(CAST(CAST(V3_T22_VIS1YY AS INT) AS VARCHAR) +'-'+CAST(CAST(V3_T22_VIS1MM AS INT) AS VARCHAR)+'-'+CAST(CAST(V3_T22_VIS1DD AS INT) AS VARCHAR)AS DATETIME) AS V3_VIS1DATE into #tmp from DV_TABLE_ALL然后这么处理就好了。。 --合法的日期SELECT V2_VIS1DATE,V3_VIS1DATE INTO T1 FROM #tmp where isdate(V2_VIS1DATE)=1 and isdate(V3_VIS1DATE)=1。。其他的以此类推。。
我的意思是:按照上面的方法处理下面这么多日期时,合并日期时就会有非常多的while条件,显然不合理。比如: --------------------- 一种不合法的情况-------------------------- --合法的日期 while( isdate(yy1+mm1+dd1))=1 and isdate(yy2+mm2+dd2))=1 ...isdate(YYn+MMn2+DDn2))=1 --如果1不合法 while( isdate(yy1+mm1+dd1))=0 and isdate(yy2+mm2+dd2))=1 ...isdate(YYn+MMn2+DDn2))=1 ..... --如果n不合法 while( isdate(yyn+mmn+ddn))=0 and isdate(yy2+mm2+dd2))=1 ...isdate(YYn+MMn+DDn))=1 ----------------------- 二种不合法的情况 ------------------------------------- ..... 这样好累!!! 实在不好弄,要求是:把日期合并重新生成到一个新表中,如果有一个合并的日期不合法,那么置为NULL。有这么多日期,怎么办??? SELECT ECODE, ENAME as INI,SN='000', V4_T20_MEDEDD_2 AS V4_MEDEDD_2, V4_T20_MEDEDD_3 AS V4_MEDEDD_3, V4_T20_MEDEDD_4 AS V4_MEDEDD_4, V4_T20_MEDEDD_5 AS V4_MEDEDD_5, V4_T20_MEDEDD_6 AS V4_MEDEDD_6, V4_T20_MEDEDD_7 AS V4_MEDEDD_7, V4_T20_MEDEDD_8 AS V4_MEDEDD_8, V4_T20_MEDEDD_9 AS V4_MEDEDD_9,V4_T20_MEDEMM_2 AS V4_MEDEMM_2, V4_T20_MEDEMM_3 AS V4_MEDEMM_3, V4_T20_MEDEMM_4 AS V4_MEDEMM_4, V4_T20_MEDEMM_5 AS V4_MEDEMM_5, V4_T20_MEDEMM_6 AS V4_MEDEMM_6, V4_T20_MEDEMM_7 AS V4_MEDEMM_7, V4_T20_MEDEMM_8 AS V4_MEDEMM_8, V4_T20_MEDEMM_9 AS V4_MEDEMM_9, V4_T20_MEDEYY_1 AS V4_MEDEYY_1,V4_T20_MEDEYY_2 AS V4_MEDEYY_2, V4_T20_MEDEYY_3 AS V4_MEDEYY_3, V4_T20_MEDEYY_4 AS V4_MEDEYY_4, V4_T20_MEDEYY_5 AS V4_MEDEYY_5, V4_T20_MEDEYY_6 AS V4_MEDEYY_6, V4_T20_MEDEYY_7 AS V4_MEDEYY_7, V4_T20_MEDEYY_8 AS V4_MEDEYY_8, V4_T20_MEDEYY_9 AS V4_MEDEYY_9, INTO med FROM DV_TABLE_ALL谢谢各位老兄!!!
你可以先把转换功能通过一个函数实现,把那些转换后的数值再插入到你的表中。 下面的代码我没有试过,你试试。create function test (@d varchar(20)) returns varchar(20) as begin declare @a varchar(20) select @a=dateadd(month,1,@d)-day(dateadd(month,1,@d)) return @a if 合法 set@a=CAST(CAST(CAST(V2_T22_VIS1YY AS INT) AS VARCHAR) +'-'+CAST(CAST(V2_T22_VIS1MM AS INT) AS VARCHAR)+'-'+CAST(CAST(V2_T22_VIS1DD AS INT) AS VARCHAR)AS DATETIME) AS V2_VIS1DATE, return @a end
/*依据年月日生成日期*/IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'ITF_BASE_BRINGDATE' AND Type = 'FN') AND SUSER_SNAME() = 'infoage' DROP FUNCTION ITF_BASE_BRINGDATE GO CREATE FUNCTION ITF_BASE_BRINGDATE( @Year INTEGER, /*年度*/ @Month INTEGER, /*月度*/ @Day INTEGER /*日期*/ ) RETURNS DATETIME WITH ENCRYPTION AS BEGIN RETURN CAST(CAST(@Year AS VARCHAR(04))+CAST(@Month AS VARCHAR(02))+CAST(@Day AS VARCHAR(02)) AS DATETIME) END GO GRANT ALL ON ITF_BASE_BRINGDATE TO PUBLIC GO
SELECT
CAST(CAST(CAST(V2_T22_VIS1YY AS INT) AS VARCHAR) +'-'+CAST(CAST(V2_T22_VIS1MM AS INT) AS VARCHAR)+'-'+CAST(CAST(V2_T22_VIS1DD AS INT) AS VARCHAR)AS DATETIME) AS V2_VIS1DATE,
CAST(CAST(CAST(V3_T22_VIS1YY AS INT) AS VARCHAR) +'-'+CAST(CAST(V3_T22_VIS1MM AS INT) AS VARCHAR)+'-'+CAST(CAST(V3_T22_VIS1DD AS INT) AS VARCHAR)AS DATETIME) AS V3_VIS1DATE
into #tmp from DV_TABLE_ALL然后这么处理就好了。。
--合法的日期SELECT
V2_VIS1DATE,V3_VIS1DATE
INTO T1 FROM #tmp
where isdate(V2_VIS1DATE)=1
and isdate(V3_VIS1DATE)=1。。其他的以此类推。。
--------------------- 一种不合法的情况--------------------------
--合法的日期
while( isdate(yy1+mm1+dd1))=1 and isdate(yy2+mm2+dd2))=1 ...isdate(YYn+MMn2+DDn2))=1
--如果1不合法
while( isdate(yy1+mm1+dd1))=0 and isdate(yy2+mm2+dd2))=1 ...isdate(YYn+MMn2+DDn2))=1
.....
--如果n不合法
while( isdate(yyn+mmn+ddn))=0 and isdate(yy2+mm2+dd2))=1 ...isdate(YYn+MMn+DDn))=1
----------------------- 二种不合法的情况 -------------------------------------
.....
这样好累!!!
实在不好弄,要求是:把日期合并重新生成到一个新表中,如果有一个合并的日期不合法,那么置为NULL。有这么多日期,怎么办???
SELECT ECODE, ENAME as INI,SN='000',
V4_T20_MEDEDD_2 AS V4_MEDEDD_2,
V4_T20_MEDEDD_3 AS V4_MEDEDD_3,
V4_T20_MEDEDD_4 AS V4_MEDEDD_4,
V4_T20_MEDEDD_5 AS V4_MEDEDD_5,
V4_T20_MEDEDD_6 AS V4_MEDEDD_6,
V4_T20_MEDEDD_7 AS V4_MEDEDD_7,
V4_T20_MEDEDD_8 AS V4_MEDEDD_8,
V4_T20_MEDEDD_9 AS V4_MEDEDD_9,V4_T20_MEDEMM_2 AS V4_MEDEMM_2,
V4_T20_MEDEMM_3 AS V4_MEDEMM_3,
V4_T20_MEDEMM_4 AS V4_MEDEMM_4,
V4_T20_MEDEMM_5 AS V4_MEDEMM_5,
V4_T20_MEDEMM_6 AS V4_MEDEMM_6,
V4_T20_MEDEMM_7 AS V4_MEDEMM_7,
V4_T20_MEDEMM_8 AS V4_MEDEMM_8,
V4_T20_MEDEMM_9 AS V4_MEDEMM_9,
V4_T20_MEDEYY_1 AS V4_MEDEYY_1,V4_T20_MEDEYY_2 AS V4_MEDEYY_2,
V4_T20_MEDEYY_3 AS V4_MEDEYY_3,
V4_T20_MEDEYY_4 AS V4_MEDEYY_4,
V4_T20_MEDEYY_5 AS V4_MEDEYY_5,
V4_T20_MEDEYY_6 AS V4_MEDEYY_6,
V4_T20_MEDEYY_7 AS V4_MEDEYY_7,
V4_T20_MEDEYY_8 AS V4_MEDEYY_8,
V4_T20_MEDEYY_9 AS V4_MEDEYY_9,
INTO med FROM DV_TABLE_ALL谢谢各位老兄!!!
下面的代码我没有试过,你试试。create function test (@d varchar(20))
returns varchar(20)
as
begin
declare @a varchar(20) select @a=dateadd(month,1,@d)-day(dateadd(month,1,@d))
return @a
if 合法
set@a=CAST(CAST(CAST(V2_T22_VIS1YY AS INT) AS VARCHAR) +'-'+CAST(CAST(V2_T22_VIS1MM AS INT) AS VARCHAR)+'-'+CAST(CAST(V2_T22_VIS1DD AS INT) AS VARCHAR)AS DATETIME) AS V2_VIS1DATE,
return @a
end
DROP FUNCTION ITF_BASE_BRINGDATE
GO
CREATE FUNCTION ITF_BASE_BRINGDATE(
@Year INTEGER, /*年度*/
@Month INTEGER, /*月度*/
@Day INTEGER /*日期*/
)
RETURNS DATETIME
WITH ENCRYPTION
AS
BEGIN
RETURN CAST(CAST(@Year AS VARCHAR(04))+CAST(@Month AS VARCHAR(02))+CAST(@Day AS VARCHAR(02)) AS DATETIME)
END
GO
GRANT ALL ON ITF_BASE_BRINGDATE TO PUBLIC
GO