SQL脚本中的日期转换

Question

我有从Oracle DB导出数据的sql文件。我需要把它转换成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 (找到替代日期模式的方法)，但是我不知道如何从行中提取日期并将其粘贴回去。

Answer 1

如果您的sed有一个-E选项来启用ERE(例如GNU和BSD )：

$ 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);