SQL - 列有DATE，如何基于“日期”获取数据作为行中的值之一？

I'm looking for an SQL query to fetch records from a table, for a particular date-format present in the column value itself.

我正在寻找一个SQL查询来从表中获取记录,以查找列值本身中存在的特定日期格式。

The column uses the format:

该列使用以下格式:

num: num1:num2:yyyy-mm-dd hh:mi:ss

Example values:

1928:52501:290c-1af6-44f2-8e3b-51ca4399
19282604:080:2901c-12-8b-51
997494:452:29a1c-1af-44-8e-51c99:2011-01-17 12:36:38
97494:452050:290c-1a-42-8b-51:2011-04-18 10:36:11

We need to fetch only those rows containing a date:

我们只需要获取包含日期的行:

997494:452:29a1c-1af-44-8e-51c99:2011-01-17 12:36:38
97494:452050:290c-1a-42-8b-51:2011-04-18 10:36:11

2 个解决方案

#1

SELECT *
FROM the_table
WHERE to_date(substr(refid, -19), 'yyyy-mm-dd HH24:MI:SS') = TIMESTAMP '2011-01-17 12:36:38'

Of course you don't need to use "real" dates you can simply use

当然,您不需要使用您可以简单使用的“真实”日期

SELECT *
FROM the_table
WHERE substr(refid, -19) = '2011-01-17 12:36:38'

If you want to find rows that have a valid date pattern at the end (instead of having this date value "2011-01-17 12:36:38") then you need a regular expression to check it:

如果要查找最后具有有效日期模式的行(而不是具有此日期值“2011-01-17 12:36:38”),则需要使用正则表达式来检查它:

SELECT * 
FROM the_table
WHERE regexp_like(refid, '([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2})$')

Note that this will not check for valid dates (it will return rows with "2011-02-31 40:50:60" at the end as well).

请注意,这不会检查有效日期(它将返回末尾带有“2011-02-31 40:50:60”的行)。

#2

It looks like the rows with a date may be distinguished simply on the basis of the number of : characters in them, e.g.:

看起来像具有日期的行可以简单地根据其中的字符数来区分,例如:

SELECT * FROM the_table
WHERE INSTR(refid, ':', 1, 3) > 0;

You could then convert the dates to Oracle dates:

然后,您可以将日期转换为Oracle日期:

SELECT TO_DATE(SUBSTR(refid,INSTR(refid,':',1,3)+1)
              ,'yyyy-mm-dd hh24:mi:ss')
FROM   the_table
WHERE  INSTR(refid,':',1,3) > 0;

But then if any row has a value that cannot be converted to a date, the query would fail.

但是,如果任何行具有无法转换为日期的值,则查询将失败。

To solve this, you could create a function to test the value, e.g.:

要解决这个问题,你可以创建一个函数来测试这个值,例如:

CREATE FUNCTION ref_date (refid IN VARCHAR2)
RETURN DATE IS
  d DATE;
BEGIN
  d := TO_DATE(SUBSTR(refid,INSTR(refid,':',1,3)+1)
              ,'yyyy-mm-dd hh24:mi:ss');
  RETURN d;
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN NULL;
END;

Now you can query it as follows:

现在您可以按如下方式查询:

SELECT ref_date(refid)
FROM   the_table
WHERE  INSTR(refid,':',1,3) > 0
AND    ref_date(refid) IS NOT NULL;

#1

SELECT *
FROM the_table
WHERE to_date(substr(refid, -19), 'yyyy-mm-dd HH24:MI:SS') = TIMESTAMP '2011-01-17 12:36:38'