如何更新存储为各种字符格式(PL/SQL)的日期? [英] How to update dates stored as varying character formats (PL/SQL)?

问题描述

问题:我有一个很大的数据库表(约50万条记录)，该表具有存储在varchar2(15)列中的日期列表.这些日期以各种格式存储，即.有些是yyyy-mm-dd，有些是mm/dd/yyyy，有些是dd/mm/yy，有些是mm/dd/yy，依此类推.

Problem: I have a large database table (~500k records) which has a list of dates stored in a varchar2(15) column. These dates are stored in varying formats, ie. some are yyyy-mm-dd, some are mm/dd/yyyy, some are dd/mm/yy, some are mm/dd/yy, etc. Ie:

1994-01-13
01/13/1994
01/13/94
13/01/94
13/01/1994
etc

我需要能够稍微移动这些日期，例如为每个日期增加30天.(这是我的目标的过分简化，但是用这种方式解释起来更容易.)

I need to be able to shift these dates slightly, for example to add 30 days to each date. (This is an oversimplification of my objective but it's easier to explain this way).

如果所有日期的格式一致，我将实现以下目标:

If all the dates were formatted consistently, I would achieve this as follows:

UPDATE history_table
    SET some_date_col = 
        to_char(to_date(some_date_col, 'mm/dd/yyyy')+30, 'mm/dd/yyyy') 
    WHERE some_date_col IS NOT NULL;

由于数据库的大小，我不能一一遍历遍历值并解析日期值.谁能建议一种无需循环即使用大量UPDATE语句来完成此操作的方法吗?

Due to the size of the database, I cannot afford to loop through the values one by one and parse the date value. Can anyone suggest a means to accomplish this without loops, ie with a mass UPDATE statement?

推荐答案

好，您这里遇到了一个真正的问题.1994年7月7日对"MM/DD/YYYY"和"DD/MM/YYYY"有效但是，在该问题之外，您可以尝试嵌套解码.我在varchar字段中输入了以下日期:

well, you've got a real problem here. 07/07/1994 is valid for 'MM/DD/YYYY' and 'DD/MM/YYYY' However, outside of that issue, you can try nesting decodes. I entered the following dates into a varchar field:

2009年12月1日，2009年12月1日，2009年1月12日，2009年1月12日

01/12/2009, 01-12-2009, 2009-01-12, 01/12/09

并使用下面的内容，我在2009年1月12日得到一致返回.您必须找出所有可能的模式，并保留嵌套解码.您可以做的另一件事是创建一个函数来处理此问题.在此功能内，您可以检查日期格式的更多细节.它也将更容易阅读.您可以在update语句中使用该函数，因此它应该比循环遍历更快，如您所述.(就其价值而言，像这样遍历500k行应该不会花很长时间.我经常必须更新具有1200万条记录的逐行表)

and using the below, I was consistently returned 1/12/2009. You'll have to figure out all the patterns possible and keep nesting decodes. The other thing you could do is create a function to handle this. Within the function, you can check with a little more detail as to the format of the date. It will also be easier to read. You can use the function in your update statement so that should be faster than looping through, as you mentioned. (for what its worth, looping through 500k rows like this shouldn't take very long. I regularly have to update row by row tables of 12 million records)

选择mydate，解码(instr(mydate，'-')，5，to_date(mydate，'YYYY-MM-DD')，3，to_date(mydate，'MM-DD-YYYY')，解码(length(mydate)，8，to_date(mydate，'MM/DD/YY')，10，to_date(mydate，'MM/DD/YYYY')))来自mydates；

select mydate, decode(instr(mydate,'-'),5,to_date(mydate,'YYYY-MM-DD'),3,to_date(mydate,'MM-DD-YYYY'), decode (length(mydate),8,to_date(mydate,'MM/DD/YY'),10,to_date(mydate,'MM/DD/YYYY'))) from mydates;

这是更新语句:

更新mydates设置revdate =解码(instr(mydate，'-')，5，to_date(mydate，'YYYY-MM-DD')，3，to_date(mydate，'MM-DD-YYYY')，解码(length(mydate)，8，to_date(mydate，'MM/DD/YY')，10，to_date(mydate，'MM/DD/YYYY')))

update mydates set revdate = decode(instr(mydate,'-'),5,to_date(mydate,'YYYY-MM-DD'),3,to_date(mydate,'MM-DD-YYYY'), decode (length(mydate),8,to_date(mydate,'MM/DD/YY'),10,to_date(mydate,'MM/DD/YYYY')))

这篇关于如何更新存储为各种字符格式(PL/SQL)的日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！