从字符串向 datetime 转换时失败

2007-12-20 11:12:55 966 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

pandas使用read_excel时，通过dtype参数调整数据类型可能失败，无法通过to_datetime或strptime将日期字符串转成datetime类型 # 导入相关模块 import numpy as np import pandas as pd from datetime import datetime from pathlib import Path pd.set_option('display.max_rows', 10) # 设置显示的最大行数 # 1、读取数据 data = pd.read_excel(Path.cwd()/f"dtype测试文件.xlsx", dtype={"上市日": str, 阅读详情

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.....

...全文

蓝牙RSSI定位实战：用Arduino+BLE4.0搭建厘米级室内定位系统（附完整代码）本文详细介绍了如何利用Arduino和BLE4.0模块构建厘米级精度的蓝牙RSSI室内定位系统。通过硬件选型、系统架构设计、三点定位算法实现及误差补偿技术，实现在商场导览、仓储管理等场景中的高精度定位。附完整代码和实测数据，帮助开发者快速部署商用级解决方案。阅读详情

idiotzeng

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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?

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,
....)

从字符串向 datetime 转换时失败

相关推荐