Search code examples
sqloracle-databasedatetimeansi-sql

Oracle TO_DATE() function - what is the universal version?

What is the universal version of Oracle SQL function TO_DATE() or TO_TIMESTAMP()?

Simple table for example:

CREATE TABLE  SAMPLE
    (
        TRUE_DATE DATE,
        CHAR_DATE VARCHAR2(10 CHAR)
    );

When using same TO_DATE() function for writing date in DATE and VARCHAR field like this

INSERT INTO SAMPLE
(TRUE_DATE, CHAR_DATE)
VALUES
(TO_DATE('1990-01-01','YYYY-MM-DD'), TO_DATE('1990-01-01','YYYY-MM-DD'));

in Oracle it will work without error. How can I write something similar, with same behavior, to work on all database systems?

Solution

  • The "universal" version would be the date and timestamp literal format as defined by the SQL standard: date '2014-09-20' or timestamp '2014-09-20 22:43:03'

    But not all DBMS support that - but more DBMS support ANSI literals than to_date()