Oracle 中用 update 语句更新date, timestamp字段的格式

最新推荐文章于 2023-04-17 09:51:18 发布

原创最新推荐文章于 2023-04-17 09:51:18 发布 · 1.5w 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#date #Date #DATE #oracle #Oracle #ORACLE #sql #SQL #TIMESTAMP

SQL 同时被 2 个专栏收录

3 篇文章

订阅专栏

Tips

3 篇文章

订阅专栏

本文介绍如何使用TO_DATE()和TO_TIMESTAMP()函数更新数据库中Date和Timestamp类型的数据，并提供了具体示例，包括日期和时间的正确格式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

更新date和timestamp数据类型的字段，分别需要用to_date()和to_timestamp()函数转换一下才可更新成功。

例：

UPDATE table_A
SET
    date_A = TO_DATE('12-11-27', 'RR-MM-DD'),
    timestamp_B = TO_TIMESTAMP('12-11-27 18:05:45.546000000', 'RR-MM-DD HH24:MI:SS.FF')
WHERE
    id = '001'

说明：

1.TIMESTAMP的毫秒数有9位数字，格式patten里面用FF对应。

2.如果时间是24小时制，格式patten里面必须写上HH24/hh24。

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

今为

关注关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

专栏目录

Oracle学习笔记（六）。数据操作语言（DML）。Oracle数据库插入DATE类型的方式。INSERT，UPDATE，DELETE语法格式

weixin_45842494的博客

01-23

4523

文章目录操作数据（DML）一、插入数据1.添加一个新行到表中1.1 INSERT语句语法1.2 指定列添加1.3 完全列添加2. 插入带空值的行2.1 隐式方式2.2 显示方式3. 插入日期3.1 使用系统时间3.2 Oracle默认的日期格式3.3 指定日期格式4.从另一个表中复制行5. 在插入表中使用默认值二、修改数据1. 改变表中的数据1.1 UPDATE语句的语法1.2 用查询更新列三、删除数据3. 从表中删除行3.1 DELETE语句语法3.2 删除基于另一个表的行3.3 删除表中的所有条件3.4

Oracle转PostgreSQL

weixin_45075336的博客

04-17

6764

Oracle转PostgreSQL PostgreSQL 13.1 手册阿里云帮助中心前言从2019年开始，就有一个很火热的话题：“去O化”。O就是oracle，也就是将oracle替换成别的数据库。原因： oracle是收费的，为了进一步降低成本；以美国为首的西方国家对华科技种种遏制行为，最近越闹越厉害，最近docker的付费服务就禁止中国企业使用；甲骨文公司中国区大幅度裁员，或将放弃中国市场也说不定； 2020年12月31起，甲骨文公司将不再对oracle11.2版本提供技术支持，

参与评论您还未登录，请先登录后发表或查看评论

Oracle sql update date类型字段

weixin_43872691的博客

03-31

5994

对于date类型字段值的更新，sql 直接写 update 表名 set 列名 = 值 where 条件这样会报错：文字与格式字符串不匹配，date字段的更新要用to_date转一下。正解： update 表名 set 列名 =to_date(‘2022-03-30’,‘yyyy-mm-dd’) where 条件 ...

oracle 时间戳TIMESTAMP

爱吃鱼油

06-03

3808

//数据库 UPDATETIMESTAMP TIMESTAMP(6) //dto /** 更新时间戳 **/ private String updatetimestamp; //dao //插入操作 INSERT INTO tablename( CUSTID, UPDATETIMESTAMP )VALUES( #{custid, jdbctyp

Oracle 中用 update 语句更新timestamp字段的格式

sll516688的博客

03-23

7290

更新timestamp数据类型的字段，分别需要用to_timestamp()函数转换一下才可更新成功。 UPDATE table set 字段名= to_timestamp('2020-04-01 23:59:59999000','YYYY-MM-DD HH24:MI:SSFF') WHERE CODE ='id'; 2020-04-01 23:59:59999000 数据库里 ...

oracle 将当前系统时间戳插入timestamp字段

lipingadjfa的博客

09-01

1624

sysdate为当前机器时间对于insert或者update语句，需要将当前时间赋值到字段中，可以使用如下函数： to_timestamp(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') ...

在插入或者更新操作的时候将date转TIMESTAMP呢

05-29

例如，MySQL的timestamp字段可以自动填充当前时间，但用户可能需要将现有的date值转为timestamp，这时候在INSERT或UPDATE语句中用函数转换。还要注意时区的问题，特别是在处理timestamp时，不同数据库的时区处理方式...

避坑必看：很详尽的MyBatis返回自增主键实验（包括插入或更新SQL语句insert on duplicate key update的自增主键返回情况）

热门推荐

花郎徒结的博客

07-20

3万+

目录 1. 实验对比维度（1）单纯的insert和insert on duplicate key update （2）selectKey和useGeneratedKeys （3）@Param和parameterType （4）单个和批量（5）keyProperty写法 2. 基本概念介绍（1）插入或更新SQL（简称InsertOrUpdate）（2）selectKey和u...

Oracle触发器原理、创建、修改、删除

crushZLX的博客

08-18

4015

本篇主要内容如下： 8.1 触发器类型 8.1.1 DML触发器 8.1.2 替代触发器 8.1.3 系统触发器 8.2创建触发器 8.2.1 触发器触发次序 8.2.2 创建DML触发器 8.2.3 创建替代(INSTEAD OF)触发器 8.2.3 创建系统事件触发器 8.2.4 系统触发器事件属性 8.2.5 使用触发器谓词 8.2.6 重新编译触发器 8.3删除和使用触发器 8.4触发器和数据字典 8.5数据库触发器的应用举例 8.6 触发器的查看 8....

Oracle迁移PostgreSQL应知应会

失语者的专栏

01-11

784

文章目录前期了解兼容性都支持但表现不一致的数据类型函数语法其他PL/SQL和PL/PgSQLjava应用端注意事项常用适配方法类型转换类型定义操作符重载方法参考文档前期了解服务器配置网络环境配置操作系统类型特殊安全和权限要求 RTO和RPO SLA RT 数据规模并发数业务使用的开发工具和开发语言开发框架兼容性都支持但表现不一致的 ORACLE PostgreSQL ‘a’||null=‘a’ ‘a’||null=null 空串和NULL相同空串和NULL不同

更新Oracle的Date字段

海阔天空

05-04

3万+

如日期格式为 2011-02-03 10:30:20 语句 update field set BEGINDATE=to_date('2015-02-03 10:30:20','yyyy-mm-dd hh24:mi:ss') where NOO='01'

ORACLE----TimeStamp(时间戳)自动更新

breakloop

12-16

2万+

之前对id的自增长进行了设置，原以为时间戳的更新也类似，使用序列之类的，但最终发现跟序列没半毛钱关系。触发器是肯定要用的，但编写脚本也是需要的。（1）创建触发器图2中，我们要选择事件（即例子中的INSERT,UPDATE），触发时间（即INSERT,UPDATE事件的前或后，例子中是before），列（例子中为MODIFIED_DATE，为TimeStamp类型），新旧值（为系统默认值OLD和NEW

Oracle update 日期

weixin_30732825的博客

09-30

226

UPDATE Sf_Msg_Monthly SET smm_Closed_Time = to_date('2011-12-30 11:54:30','yyyy-MM-dd HH24:MI:SS') WHERE smm_Monthlyid = 1; 转载于:https://www.cnblogs.com/wangfuyou/p/5923271.html...

MySQL timestamp自动更新时间分享

技术学习笔记

09-18

776

通常表中会有一个Create date 创建日期的字段，其它数据库均有默认值的选项。MySQL也有默认值timestamp，但在MySQL中，不仅是插入就算是修改也会更新timestamp的值！这样一来，就不是创建日期了，当作更新日期来使用比较好！因此在MySQL中要记录创建日期还得使用datetime 然后使用NOW() 函数完成！ 1，TIMESTAMP DEFAULT

Oracle中CREATE_TIME和UPDATE_TIME

小张的博客

04-17

1612

oracle中create_time中时间类型设置为date，添加默认值。oracle中update_time则需要设置触发器。

PostgresSQL设置表中update_time字段在更新时timestamp自动更新

icee

04-22

5609

PostgresSQL设置表中update_time字段在更新时timestamp自动更新环境： Centos 7.5 使用Navicat打开Mysql数据库时，在设计表的里面值类型为timestamp的字段中可以勾选根据当前时间戳更新，即可实现在表数据有更新的时候自动更新时间戳。那么Navicat打开PostgresSQL数据库时并没有类似方便的操作，该如何实现呢？答案是设置触发器来达到...

oracle对表的update_time(更新时间)做修改自动更新

u011192622的博客

11-08

4816

例如: 需要对表bs_cpws 的update_time 字段做自动更新可新建触发器如下： create or replace trigger trig_bs_cpws_auto_update_time # 触发器名称 before update on bs_cpws #表名 for each row begin select sysdate into :NEW.update_time from dual; end; ...

oracle update. date,Oracle date corruption during update

weixin_39915668的博客

04-14

180

问题I'm migrating some data from one oracle schema/table to a new schema/table on the same database.The migration script does the following:create table newtable as select...cast(ACTIVITYDATE as date) a...

### Cause: java.sql.SQLSyntaxErrorException: ORA-01790: 式には対応する式と同じデータ型を持つ必要があります ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-01790: 式には対応する式と同じデータ型を持つ必要があります ] with root cause Error : 1790, Position : 213, Sql = MERGE INTO A01_PROJECT_MEMBER t USING ( SELECT :1 AS PROJECT_ID, :2 AS EMPLOYEE_ID, :3 AS EMPLOYEE_NAME, :4 AS ROLE, :5 AS START_DATE, :6 AS END_DATE, 0 AS DEL_FLG, :7 AS INSERT_USER_ID, :8 AS UPDATE_USER_ID FROM dual UNION ALL SELECT :9 AS PROJECT_ID, :10 AS EMPLOYEE_ID, :11 AS EMPLOYEE_NAME, :12 AS ROLE, :13 AS START_DATE, :14 AS END_DATE, 0 AS DEL_FLG, :15 AS INSERT_USER_ID, :16 AS UPDATE_USER_ID FROM dual ) s ON ( t.PROJECT_ID = s.PROJECT_ID AND t.EMPLOYEE_ID = s.EMPLOYEE_ID AND t.START_DATE = s.START_DATE ) WHEN MATCHED THEN UPDATE SET t.EMPLOYEE_NAME = s.EMPLOYEE_NAME, t.ROLE = s.ROLE, t.END_DATE = CURRENT_TIMESTAMP, t.DEL_FLG = s.DEL_FLG, t.INSERT_USER_ID = s.INSERT_USER_ID, t.INSERT_TIME = t.INSERT_TIME, t.UPDATE_USER_ID = s.UPDATE_USER_ID, t.UPDATE_TIME = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT ( PROJECT_ID, EMPLOYEE_ID, EMPLOYEE_NAME, ROLE, START_DATE, END_DATE, DEL_FLG, INSERT_USER_ID, INSERT_TIME, UPDATE_USER_ID, UPDATE_TIME ) VALUES ( s.PROJECT_ID, s.EMPLOYEE_ID, s.EMPLOYEE_NAME, s.ROLE, 　　　CURRENT_TIMESTAMP, null, s.DEL_FLG, s.INSERT_USER_ID, CURRENT_TIMESTAMP, s.UPDATE_USER_ID, CURRENT_TIMESTAMP ), OriginalSql = MERGE INTO A01_PROJECT_MEMBER t USING ( SELECT ? AS PROJECT_ID, ? AS EMPLOYEE_ID, ? AS EMPLOYEE_NAME, ? AS ROLE, ? AS START_DATE, ? AS END_DATE, 0 AS DEL_FLG, ? AS INSERT_USER_ID, ? AS UPDATE_USER_ID FROM dual UNION ALL SELECT ? AS PROJECT_ID, ? AS EMPLOYEE_ID, ? AS EMPLOYEE_NAME, ? AS ROLE, ? AS START_DATE, ? AS END_DATE, 0 AS DEL_FLG, ? AS INSERT_USER_ID, ? AS UPDATE_USER_ID FROM dual ) s ON ( t.PROJECT_ID = s.PROJECT_ID AND t.EMPLOYEE_ID = s.EMPLOYEE_ID AND t.START_DATE = s.START_DATE ) WHEN MATCHED THEN UPDATE SET t.EMPLOYEE_NAME = s.EMPLOYEE_NAME, t.ROLE = s.ROLE, t.END_DATE = CURRENT_TIMESTAMP, t.DEL_FLG = s.DEL_FLG, t.INSERT_USER_ID = s.INSERT_USER_ID, t.INSERT_TIME = t.INSERT_TIME, t.UPDATE_USER_ID = s.UPDATE_USER_ID, t.UPDATE_TIME = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT ( PROJECT_ID, EMPLOYEE_ID, EMPLOYEE_NAME, ROLE, START_DATE, END_DATE, DEL_FLG, INSERT_USER_ID, INSERT_TIME, UPDATE_USER_ID, UPDATE_TIME ) VALUES ( s.PROJECT_ID, s.EMPLOYEE_ID, s.EMPLOYEE_NAME, s.ROLE, 　　　CURRENT_TIMESTAMP, null, s.DEL_FLG, s.INSERT_USER_ID, CURRENT_TIMESTAMP, s.UPDATE_USER_ID, CURRENT_TIMESTAMP ), Error Msg = ORA-01790: 式には対応する式と同じデータ型を持つ必要があります哪个地方报错了

最新发布

07-25

<think>我们正在处理一个特定的Oracle错误：ORA-01790。根据提供的引用，这个错误通常发生在SQL表达式中数据类型不匹配时。下面我将详细解释这个错误的原因、位置以及解决方法。 ### 错误解释 **ORA-01790: 表达式必须具有与对应表达式相同的数据类型** 这个错误发生在当Oracle期望两个或多个表达式具有相同的数据类型，但实际却不同时。常见于以下情况： - 使用`UNION`、`UNION ALL`、`INTERSECT`或`MINUS`等集合操作时，每个`SELECT`语句的对应列的数据类型必须一致。 - 在插入数据时，插入的值与目标列的数据类型不匹配（尤其是在使用动态SQL或ORM框架时，如MyBatis）。 - 在`CASE`表达式或`DECODE`函数中，各个分支返回的数据类型不一致。 ### 错误位置分析根据引用[1]和[2]： - 在插入数据时，如果某个字段被赋予了`NULL`值，而该字段在数据库中是`NUMBER`类型，那么MyBatis（或其他ORM）可能无法准确推断`NULL`的类型，导致类型转换错误。 - 在批量插入时，如果使用`UNION ALL`将多个`SELECT`语句组合起来，那么每个`SELECT`语句的对应列必须具有相同的数据类型。如果某个列在某个子查询中为`NULL`，且没有指定其类型，Oracle将无法确定其数据类型，从而引发此错误。引用[3]中的示例代码： ```xml <insert id="insertBatch"> insert into T_INVENTORY_AUTH_HIS (HIS_OPER_ID, HIS_OPER_DATE, HIS_OPER_TYPE, AUTH_ID, INVENTORY_ID, USER_ID, AUTH_TYPE, AREA_ID) <foreach collection="inventoryAuthHisList" item="item" open="(" separator=" union all " close=")"> select #{item.hisOperId}, #{item.hisOperDate}, #{item.hisOperType}, #{item.authId}, #{item.inventoryId}, #{item.userId}, #{item.authType}, #{item.areaId,jdbcType=DOUBLE} from dual </foreach> </insert> ``` 这里，`AREA_ID`字段在插入时指定了`jdbcType=DOUBLE`，而其他字段没有指定。这是因为其他字段可能为非空，而`AREA_ID`可能为空。当某个字段可能为`NULL`时，必须显式指定其JDBC类型，以便MyBatis能正确转换。 ### 解决方法 1. **显式指定JDBC类型**（针对MyBatis）：在MyBatis的映射文件中，对于可能为`NULL`的参数，使用`jdbcType`属性指定其类型。例如： ```xml #{item.areaId, jdbcType=DOUBLE} ``` 这样，即使`areaId`为`NULL`，MyBatis也会将其作为`DOUBLE`类型处理，与数据库表定义的`NUMBER`类型匹配。 2. **检查并统一集合操作中的数据类型**：如果使用`UNION ALL`进行批量插入，确保每个`SELECT`子句的对应列具有相同的数据类型。例如： ```sql SELECT 1, 'ABC' FROM DUAL UNION ALL SELECT 2, NULL FROM DUAL; -- 这里NULL会导致类型不明确 ``` 可以显式转换`NULL`的类型： ```sql SELECT 1, 'ABC' FROM DUAL UNION ALL SELECT 2, CAST(NULL AS VARCHAR2(10)) FROM DUAL; ``` 3. **检查表定义和插入值的数据类型**：确保插入的值与目标列的数据类型兼容。特别是当插入`NULL`时，要确保ORM框架能正确推断类型，或者显式指定类型。 4. **使用`NVL`函数提供默认值**（可选）：如果业务允许，可以用`NVL`函数将`NULL`转换为一个非空值，并明确类型： ```sql INSERT INTO table (col1) VALUES (NVL(#{value}, 0)); -- 假设col1是NUMBER类型 ``` ### 总结 - **根本原因**：`NULL`值在SQL表达式中无法推断出确切的数据类型，导致与目标列类型不匹配。 - **解决关键**：在可能为`NULL`的地方显式指定数据类型（如MyBatis中用`jdbcType`属性）。 - **适用场景**：使用MyBatis等ORM框架批量插入数据时，或使用`UNION ALL`等集合操作时。通过以上方法，可以避免ORA-01790错误。在实际开发中，建议对所有可能为`NULL`的插入参数都显式指定`jdbcType`属性[^1][^2][^3]。 --- ### 相关问题 1. 在Oracle中，如何批量插入大量数据以提高性能？ 2. MyBatis中还有哪些常见的数据库错误需要特别注意？ 3. 如何优化使用`UNION ALL`的SQL查询性能？ 4. Oracle中`NULL`值处理的最佳实践有哪些？ 5. 在分布式事务中，如何保证Oracle数据库插入操作的原子性？</think>### ORA-01790 错误详解 #### **错误本质** **ORA-01790: 表达式必须具有与对应表达式相同的数据类型** 表示SQL语句中存在**数据类型不匹配**的问题，通常发生在需要数据类型严格一致的场景中[^1][^2]。 --- ### **错误位置定位** 以下场景会触发此错误： 1. **`UNION`/`UNION ALL`操作** 多个`SELECT`语句的对应列数据类型必须完全一致： ```sql -- 错误示例：NUMBER 与 VARCHAR2 混用 SELECT 1 AS id FROM dual UNION ALL SELECT 'A' AS id FROM dual; -- 触发ORA-01790 ``` 2. **批量插入时的`NULL`值处理**（常见于MyBatis）当插入`NULL`值时，ORM框架无法自动推断数据类型： ```xml  #{item.areaId} -- 若areaId为NULL且未指定jdbcType ``` 3. **`CASE`表达式或函数参数** 所有分支返回值必须类型兼容： ```sql -- 错误示例：NUMBER 与 DATE 混用 SELECT CASE WHEN 1=1 THEN 100 ELSE SYSDATE END FROM dual; ``` --- ### **根本原因** | 场景 | 根本原因 | |---------------------|--------------------------------------------------------------------------| | `UNION ALL`批量插入 | 各子查询的对应列数据类型不一致 | | ORM框架处理`NULL` | 框架无法推断`NULL`的具体类型（如未指定`jdbcType`） | | 表连接/子查询 | 关联字段数据类型不匹配（如`VARCHAR2(10)`连接`NUMBER`） | --- ### **解决方案** #### 1. 显式指定`NULL`的数据类型（MyBatis关键方案） ```xml <foreach collection="list" item="item"> SELECT #{item.numField, jdbcType=NUMERIC},  #{item.nullField, jdbcType=VARCHAR}  FROM dual </foreach> ``` > **必须**为可能为`NULL`的字段添加`jdbcType`属性[^2][^3] #### 2. 统一`UNION ALL`子查询的数据类型 ```sql SELECT CAST(NULL AS NUMBER) AS id, -- 显式转换NULL类型 CAST('ABC' AS VARCHAR2(10)) AS name FROM dual UNION ALL SELECT employee_id, TO_CHAR(hire_date) -- 统一转为字符串 FROM employees; ``` #### 3. 检查并修正表结构 ```sql -- 确认字段实际类型 SELECT column_name, data_type FROM all_tab_columns WHERE table_name = 'YOUR_TABLE'; ``` --- ### **预防措施** 1. **ORM框架规范** 所有映射参数必须声明`jdbcType`，尤其可为空的字段： ```xml #{field, jdbcType=VARCHAR} ``` 2. **SQL开发规范** - 使用`CAST()`或`TO_CHAR()`/`TO_NUMBER()`统一类型 - 避免隐式类型转换 3. **数据库设计** - 关联字段保持相同数据类型 - 对常用字段（如ID、CODE）建立统一类型标准 > 经验表明：**90%的ORA-01790错误源于未指定`NULL`的`jdbcType`**[^2][^3] --- ### 相关问题 1. 如何在MyBatis中全局配置`jdbcType`以避免空值错误？ 2. Oracle隐式类型转换的规则有哪些？ 3. 使用`CAST()`函数时可能遇到哪些性能问题？ 4. 如何批量修改Oracle表字段的数据类型？ 5. 在分布式系统中如何保证不同服务的数据类型一致性？ [引用来源] [^1]: ORA-01790在插入NULL值时的触发场景 [^2]: MyBatis批量插入时未指定jdbcType导致类型推断失败 [^3]: 通过显式声明jdbcType解决UNION ALL类型冲突