3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
从字符串向 datetime 转换时失败
sql 语句如下：
INSERT INTO TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ") SELECT 1, "XXID", '010000', CAST(39415 as datetime), CAST(39445 as datetime) FROM TABLEB
AID,BID为整数类型
COLUMNA为文本
SSQS,SSQZ为日期型
另外尝试过一下SQL，均报同样错误
INSERT INTO TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ") SELECT 1, "XXID", '010000', CONVERT(datetime, '2007-12-01T00:00:00', 126), CONVERT(datetime, '2007-12-31T00:00:00', 126) FROM TABLEB
INSERT INTO TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ") SELECT 1, "XXID", '010000', CONVERT(datetime, '20071201', 112), CONVERT(datetime, '20071231', 112) FROM TABLEB
INSERT INTO TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ") SELECT 1, "XXID", '010000', @SSQS, @SSQZ FROM TABLEB
AddInParameter.....
INSERT INTO TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ") SELECT 1, "XXID", '010000', CAST(39415 as datetime), CAST(39445 as datetime) FROM TABLEB
AID,BID为整数类型
COLUMNA为文本
SSQS,SSQZ为日期型
另外尝试过一下SQL，均报同样错误
INSERT INTO TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ") SELECT 1, "XXID", '010000', CONVERT(datetime, '2007-12-01T00:00:00', 126), CONVERT(datetime, '2007-12-31T00:00:00', 126) FROM TABLEB
INSERT INTO TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ") SELECT 1, "XXID", '010000', CONVERT(datetime, '20071201', 112), CONVERT(datetime, '20071231', 112) FROM TABLEB
INSERT INTO TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ") SELECT 1, "XXID", '010000', @SSQS, @SSQZ FROM TABLEB
AddInParameter.....
...全文
请发表友善的回复…
发表回复
fcuandy 2007-12-20
列名加""是可以的,功能同[].
SQL报的错不一定是准确的.
我估计错误的原因在于:
INSERT INTO TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ") SELECT 1, "XXID", '010000', CAST(39415 as datetime), CAST(39445 as datetime) FROM TABLEB
tableA.bid 是int型
tableB.xxid 是否为int?
SQL报的错不一定是准确的.
我估计错误的原因在于:
INSERT INTO TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ") SELECT 1, "XXID", '010000', CAST(39415 as datetime), CAST(39445 as datetime) FROM TABLEB
tableA.bid 是int型
tableB.xxid 是否为int?
winstonbonaparte 2007-12-20
我写的那个例子都可以正确执行的.LZ是不是字段类型弄错了
dawugui 2007-12-20
我看也没问题.
-狙击手- 2007-12-20
似乎没问题呀
dobear_0922 2007-12-20
INSERT INTO TABLEA(AID, BID, COLUMNA, SSQS, SSQZ)
SELECT 1, 'XXID', '010000', CAST(39415 as datetime), CAST(39445 as datetime) FROM TABLEB dobear_0922 2007-12-20
列名不需要加""，字符前后加''
INSERT INTO TABLEA(AID, BID, COLUMNA, SSQS, SSQZ) SELECT 1, 'XXID', '010000', CAST(39415 as datetime), CAST(39445 as datetime) FROM TABLEB winstonbonaparte 2007-12-20
create table #TABLEA
(
AID int,
BID int,
COLUMNA varchar(20),
SSQS datetime,
SSQZ datetime
)
INSERT INTO #TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ")
SELECT 1, 3, '010000', CAST(39415 as datetime), CAST(39445 as datetime)
go
INSERT INTO #TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ")
SELECT 1, 1, '010000', CONVERT(datetime, '2007-12-01T00:00:00', 126), CONVERT(datetime, '2007-12-31T00:00:00', 126)
go
INSERT INTO #TABLEA("AID", "BID", "COLUMNA", "SSQS", "SSQZ")
SELECT 1, 2, '010000', CONVERT(datetime, '20071201', 112), CONVERT(datetime, '20071231', 112)
go
select * from #TABLEA
drop table #TABLEA
--------------------------
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 3 行）
AID BID COLUMNA SSQS SSQZ
1 3 010000 2007-12-01 00:00:00.000 2007-12-31 00:00:00.000
1 1 010000 2007-12-01 00:00:00.000 2007-12-31 00:00:00.000
1 2 010000 2007-12-01 00:00:00.000 2007-12-31 00:00:00.000
dobear_0922 2007-12-20
select dt1=CAST(39415 as datetime)
,dt2=CONVERT(datetime, '20071201', 112)
,dt3=CONVERT(datetime, '2007-12-01T00:00:00', 126)
/*
dt1 dt2 dt3
----------------------- ----------------------- -----------------------
2007-12-01 00:00:00.000 2007-12-01 00:00:00.000 2007-12-01 00:00:00.000
(1 row(s) affected)
*/dobear_0922 2007-12-20
select CAST(39415 as datetime), CAST(39445 as datetime)
/*
----------------------- -----------------------
2007-12-01 00:00:00.000 2007-12-31 00:00:00.000
(1 row(s) affected)
*/idiotzeng 2007-12-20
谢谢，找到问题了，跟SQL语句没关系，创建表的SQL有问题
create table xxx(
....
"XXRQ" datetime default '0' null,
....)
create table xxx(
....
"XXRQ" datetime default '0' null,
....)