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
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
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).
For example:
SELECT TO_DATE( '30/06/14', 'dd/mm/yy' ) AS date1,
TO_DATE( '30/06/14', 'dd/mm/yyyy' ) AS date2
FROM DUAL;
Outputs:
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
or
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
