☰

SQL脚本中的日期转换

tag-icon • tag-icon linux awk sed
SQL脚本中的日期转换

我有 sql 文件，其中包含从 Oracle DB 导出的数据。我需要将其转换为 MSSQL 格式。现在我正在努力解决日期转换问题。

样品线：

Insert into "schema_version" ("installed_rank","version","description","type","script","checksum","installed_by","installed_on","execution_time","success") values (1,'1.0','schema','SQL','V1_0__schema.sql',-839661171,'HYP_EPMO2',to_timestamp('11/09/18 15:42:42,712463000','DD/MM/RR HH24:MI:SS,FF'),496,1);

我需要转换to_timestamp('11/09/18 15:42:42,712463000','DD/MM/RR HH24:MI:SS,FF')成2018-09-11T15:42:42.我怎样才能以简单的方式做到这一点？我在几个表中遇到同样的问题，因此无法按列号进行标记。到目前为止，我正在使用sed其他转换，但在这里找不到好的方法。我也在尝试awk（找到了替换日期模式的方法），但我不知道如何从行中仅提取日期并将其粘贴回来，

答案1

如果您的 sed 有-E启用 ERE 的选项（例如 GNU 和 BSD sed）：

$ sed -E "s:(.*)to_timestamp\('([^/]+)/([^/]+)/([^/]+) ([^,]+)[^)]+\):\1'20\4-\2-\3T\5':" file
Insert into "schema_version" ("installed_rank","version","description","type","script","checksum","installed_by","installed_on","execution_time","success") values (1,'1.0','schema','SQL','V1_0__schema.sql',-839661171,'HYP_EPMO2','2018-11-09T15:42:42',496,1);

否则使用任何 sed：

$ sed "s:\(.*\)to_timestamp('\([^/]*\)/\([^/]*\)/\([^/]*\) \([^,]*\)[^)]*):\1'20\4-\2-\3T\5':" file
Insert into "schema_version" ("installed_rank","version","description","type","script","checksum","installed_by","installed_on","execution_time","success") values (1,'1.0','schema','SQL','V1_0__schema.sql',-839661171,'HYP_EPMO2','2018-11-09T15:42:42',496,1);

相关内容