Date conversion is not correct in unix

Question

In sql dev, date conversion is correct but when I put it in unix script, date conversion is different.

TO_CHAR(TO_DATE(MAX(DATE), 'DD-MON-YYYY HH24:MI:SS'), 'YYYY-MM-DD"T"HH24:MI:SS')

Input: 01-SEP-2018 21:30:05
Result in sql dev: 2018-09-01T21:30:05
Result in unix script: 0018-09-01T00:00:00

EDIT: Other Details

Below is update_date.sh in Unix. This is already working (temp tables drop/created with rows I need). But contains then following: when I try to convert A.Date (which is a date), result is not correct.

0018-09-01T00:00:00. Year became 0018 and Time became 00:00:00.

#!/bin/sh

ACCOUNT=${APP_DB_USER}/${APP_DB_PASS}@${APP_DB_INST}

sqlplus -s $ACCOUNT << EOF > update_date.log

SET HEADING OFF;
WHENEVER SQLERROR CONTINUE;

SET ECHO ON;
SET SERVEROUTPUT ON SIZE 1000000;
SET TIME ON;
SET TIMING ON;

prompt TEMP TABLE: TMP_LOGS
        DROP TABLE TMP_LOGS;
        COMMIT;
        CREATE TABLE "TMP_MISMATCH_DATE"
                ("ID"       VARCHAR2(200 BYTE),
                "VALUE"     VARCHAR2(4000 BYTE),
                "COMPLETED" DATE,
                "REMARKS"   VARCHAR2(4000 BYTE));
        COMMIT;

prompt TEMP TABLE: TMP_MISMATCH_DATE

        DROP TABLE TMP_MISMATCH_DATE;
        COMMIT;
        CREATE TABLE TMP_MISMATCH_DATE AS
        SELECT /*+PARALLEL(A,8) PARALLEL(B,8) PARALLEL(C,8)*/
                A.ID,
                MAX(A.START_DATE),
                TO_CHAR(TO_DATE(MAX(A.DATE), 'DD-MON-YYYY HH24:MI:SS'), 'YYYY-MM-DD"T"HH24:MI:SS') as FIN_DATE,
                C.NAME,
                C.VALUE
        FROM TABLE1 A
                INNER JOIN temp_table B ON A.ID=B.ID
                INNER JOIN TABLE2 C ON A.ID=C.ID
        WHERE
                A.IND = 'N' AND A.STATUS = 'PR' AND
                C.NAME='SomeName' AND C.EXP_DATE IS NULL
                GROUP BY A.ID, C.NAME, C.VALUE;
        COMMIT;

prompt UPDATE Value 'SomeName' IN TABLE2

DECLARE
        CURSOR CUSTCUR IS
                 SELECT ID, START_DATE, FIN_DATE, NAME, VALUE
                 FROM TMP_MISMATCH_DATE
                 WHERE FIN_DATE IS NOT NULL;
BEGIN
        FOR CURREC IN CUSTCUR LOOP

                UPDATE TABLE2 agrprm
                SET agrprm.VALUE = curRec.FIN_DATE,
                    agrprm.update_date = SYSDATE
                WHERE
                        agrprm.ID = curRec.ID AND
                        agrprm.NAME = 'SomeName' AND
                        agrprm.EXP_DATE IS NULL
                ;
                COMMIT;

                INSERT INTO TMP_LOGS
                        VALUES( CURREC.ID,
                                CURREC.VALUES,
                                SYSDATE, 'NEW_VALUE');
                COMMIT;
        END LOOP;
        COMMIT;
END;
/
EXIT;
EOF

Can you show us the Unix "script" (whatever that means) - the line of code where this conversion happens? — , Sep 01 '18 at 05:05
What is the input value or data type of `DATE`? Is your input really `01-SEP-2018 21:30:05`? — Wernfried Domscheit, Sep 01 '18 at 06:35
**NEVER** call `to_date()` on a value that is already a `DATE`. You first convert it to a `varchar` just to convert it back to a `DATE` which it was to begin with. If `DATE` is a real `DATE` use `TO_CHAR(MAX(DATE), 'YYYY-MM-DD"T"HH24:MI:SS')` — , Sep 01 '18 at 06:47
OK. In the shell script, you show `ACCOUNT=${APP_DB_USER}/${APP_DB_PASS}@${APP_DB_INST}`. When you work in SQL Developer, are you using the same credentials? The problem is that when you call `TO_DATE()` on something that is already a date, Oracle will first convert that date to a string (implicitly and using the session's `nls_date_format` parameter). If you are using a different account, the default `nls_date_format` may be different (it may be 'DD-MON-RR' for the shell script account), which would explain what you are seeing. — , Sep 02 '18 at 05:04
@mathguy yup, it's just the same account. oh i think i got it already! there's no need to do TO_DATE(). i can proceed with TO_CHAR(MAX(DATE), 'YYYY-MM-DD"T"HH24:MI:SS') — dcdum2018, Sep 02 '18 at 05:28
Right, but that doesn't explain why it works in SQL Developer. Except... yeah - SQL Developer has its own saved NLS_DATE_FORMAT in the Preferences, if you look you will see it must have four-digit year. (Under Tools - Preferences - Database - NLS.) This is like having a `login.sql` script in SQL\*Plus. This must be why it works in SQL Developer, even with that mistake in the code. — , Sep 02 '18 at 05:49
For future reference, in Oracle you don't need COMMIT after executing DDL, as changes to the data dictionary are committed automatically. Also, it's better to simply TRUNCATE tables rather than dropping and re-creating them. — APC, Sep 02 '18 at 13:48

score 1 · Accepted Answer · answered Sep 03 '18 at 06:38

Be careful, in the above code you gave, there are issues:

The DATE field you call in MAX(A.DATE) does not exist. I think you meant COMPLETED instead
The VALUES field you call in CURREC.VALUES doesn't exist either. maybe rathe VALUE?

So the solution you needed:

Instead of converting a date to a varchar then back to a date with,

TO_CHAR(
   TO_DATE(
       MAX(DATE)
       , 'DD-MON-YYYY HH24:MI:SS')
 , 'YYYY-MM-DD"T"HH24:MI:SS')

You can just proceed with

TO_CHAR(
      MAX(DATE)
    , 'YYYY-MM-DD"T"HH24:MI:SS')

yes, i just removed TO_DATE() from TO_CHAR(TO_DATE()); – dcdum2018 Sep 04 '18 at 09:31 — dcdum2018, Sep 04 '18 at 09:31

Date conversion is not correct in unix

1 Answers1

So the solution you needed: