Search code examples
sqloracledatedate-formatto-date

Use of DATE type in SQL/PLSQL

I found the following code wrritten by one of my colleague which runs perfectly.

UPDATE STUDENT_COURSE_INTERMISSION SCI
SET END_DT = '25-MAY-2024' 
where END_DT = '28-MAY-2024'

I recomended to change the code to the following (use TO_DATE with a date format instead of passing a text)

UPDATE STUDENT_COURSE_INTERMISSION SCI
SET END_DT = TO_DATE('25/05/2024', 'dd/mm/yyyy')
where END_DT = TO_DATE('28/05/2024', 'dd/mm/yyyy')

In the database END_DT is in DATE format and looks like following.

enter image description here

Is it ok to use dates without casting them like in the first code sample ? I really appreciate your openion on this as both works fine.

PS - As far as I know we can use both of the following ways for DATEs

  • TO_DATE(date, format)
  • DATE(date)

Thank you

Solution

  • It is very unclean and risky to do not check for a proper date and can easily lead to issues.

    For example, let's assume we are in Japan now:

    ALTER SESSION SET NLS_TERRITORY = 'Japan';

    Then the bad idea to use a string as "date" as you mentioned in your question will no longer work. If we try to execute following query...

    SELECT yourcolumn
FROM yourtable
WHERE yourcolumn = '24-JULY-2023';

    ...we will get an error like this:

    ORA-01858: A non-numeric character was found instead of a numeric character.

    Using proper TO_DATE will work correctly, for example this command will successfully update the rows with today's date:

    UPDATE yourtable
SET yourcolumn = TO_DATE('2024-07-31','YYYY-MM-DD') 
WHERE yourcolumn = TO_DATE('2023-07-24','YYYY-MM-DD');

    See this fiddle example, it shows this behaviour.