Use of DATE type in SQL/PLSQL

Question

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.

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

Answer 1

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.

Answer 2

Its dependent on the SQL Servers implementation which formats are supported and in which format they are saved.

Most of the times, multiple different formats are supported, but one is chosen to store the data in the db itself.

There is no difference in speed or reduced complexity when updating your querys, since the DB will always try to format your input.

Hope this helps.

Answer 3

Generally it is best to avoid implicit conversions, such as trying to convert from varchar to date, which may fail in case the nls_date_format for that database session does not correspond to what is passed as string. Thus, use to_date and to_timestamp. Also, for the to_char apply the desired format mask so that you may have consistent results irrespective of the nls_date_format. For dates, there is also the possibility of using date '2023-10-23' (yyyy-mm-dd format).