仅使用PL/SQL从datetime字段(从notifyix数据库)读取日期 [英] Reading date only with PL/SQL from datetime field , from informix database

问题描述

我在使用PL/SQL仅读取日期时遇到问题.我要读取的字段是日期时间，其名称是last_updated.我正在比较oracle和notifyix数据库，这就是为什么我使用pl/sql的原因. 我有一个查询，该查询看起来必须工作，并且像这样:

I am having problem reading only the date with PL/SQL. The field that i want to read is datetime and its name is last_updated. I am doing comparation between oracle and informix databases and thats why i am using pl/sql. I have a query that looks and must work and its like this:

        select to_date("last_updated", 'dd/mm/YYYY')   from "smetka"@informix

此查询正在返回结果，并且没有给出任何错误，但是日期的格式类似于此30.06. 0 014，并且必须为30.06. 2 014

This query is returning results, and it dont gives any error, but the format of the date is like this 30.06.0014 and must be 30.06.2014

推荐答案

您可能会发现数据库希望使用格式dd/mm/yyyy输入日期，但是任何存放的应用程序都使用格式dd/mm/yy并尝试格式化它使用dd/mm/yyyy将该数据库接受为1世纪(而不是21世纪的预期).

You will probably find that the database expected the date to be entered using the format dd/mm/yyyy but whatever application deposited used the format dd/mm/yy and trying to format it using dd/mm/yyyy the database just accepts it as being in the 1st century (rather than as expected the 21st century).

例如:

SELECT TO_DATE( '30/06/14', 'dd/mm/yy' )   AS date1,
       TO_DATE( '30/06/14', 'dd/mm/yyyy' ) AS date2
FROM   DUAL;

输出:

DATE1               DATE2
------------------- -------------------
2014-06-30 00:00:00 0014-06-30 00:00:00

您可以尝试使用类似以下的方法来纠正它:

You could try to correct it using something like this:

SELECT CASE
       WHEN TO_DATE( "last_updated", 'dd/mm/yyyy' )
              BETWEEN DATE '0000-01-01' AND DATE 0100-01-01'
       THEN TO_DATE( "last_updated", 'dd/mm/yyyy' ) + INTERVAL '2000' YEAR(4)
       ELSE TO_DATE( "last_updated", 'dd/mm/yyyy' )
       END
FROM   "smetka"@informix

或

SELECT CASE
       WHEN REGEXP_LIKE( "last_updated", '\d{1,2}/\d{1,2}/\d{1,2}' )
       THEN TO_DATE( "last_updated", 'dd/mm/yy' )
       ELSE TO_DATE( "last_updated", 'dd/mm/yyyy' )
       END
FROM   "smetka"@informix

这篇关于仅使用PL/SQL从datetime字段(从notifyix数据库)读取日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！