Should date formats be specified in SQL statements?

Question

I see code from developers using implicit date conversion. I would like a definitive answer to why they should not do this.

SELECT * from dba_objects WHERE Created >= '06-MAR-2012';

Answer 1

Because '2012/12/1' in the US is 11 months after the same string date in Europe.

Allowing implicit conversions means you are at the mercy of location settings.

If you can name a business where 11 months is an acceptable margin of error I'll be impressed.

Answer 2

There are problems that will occur if a session with a different date format runs the code.

Statement Failure

DROP TABLE t1;
CREATE TABLE t1 AS (SELECT sysdate mydate FROM dual WHERE 1=2);
ALTER SESSION SET NLS_DATE_FORMAT = 'MON-DD-RR';
INSERT INTO t1 VALUES ('01-02-12');
                       *
ERROR at line 1:
ORA-01843: not a valid month

Bad Data

  DROP TABLE t1;
  CREATE TABLE t1 AS (SELECT sysdate mydate FROM dual WHERE 1=2);

  --User 1
  ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-RR';
  INSERT INTO t1 VALUES ('01-02-11');

  --User 2
  ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-RR';
  INSERT INTO t1 VALUES ('01-02-11');

  --User 3
  ALTER SESSION SET NLS_DATE_FORMAT = 'RR-MM-DD';
  INSERT INTO t1 VALUES ('01-02-11');

  SELECT to_char(mydate,'MM/DD/YYYY') FROM t1;

In this situation because each of the alter/insert statements could be done by different users. They would all be running the same statements, but the resulting dates would be completely different. The insert statements might be buried in a package that is only indirectly being called. Because no error was returned the problem might not be found until much later.

SQL Injection

  CLEAR SCREEN;
  DROP TABLE Secrets;
  CREATE TABLE Secrets (RevealDate Date, Secret Varchar2(200));
  INSERT INTO Secrets VALUES (trunc(sysdate),   '*** Common Knowledge. ***');
  INSERT INTO Secrets VALUES (trunc(sysdate+1), '*** Don''t Let Anyone know this. ***');

  CREATE OR REPLACE PROCEDURE ShowRevealedSecrets IS
     vStatement varchar2(200);
     vOutput Varchar2(1000);
     vDate date:=sysdate;
  begin
  vStatement:='SELECT secret FROM Secrets WHERE RevealDate = ''' || vDate || '''';
  execute immediate vStatement INTO vOutput;
  DBMS_Output.Put_Line(vOutput);
  END;
  /

  --Normal Use.     
  ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';
  EXEC ShowRevealedSecrets();

  --Explointing SQL Injection
  ALTER SESSION SET NLS_DATE_FORMAT = '"'' OR RevealDate > sysdate--"';
  EXEC ShowRevealedSecrets();

In this situation a malicious individual could alter there sessions date format in such a way as to give them access to data that they would not normally have access to.

Answer 3

I would like a definitive answer to why they should not do this.

I would say you will not find a definitive answer.

Many of the answers already given here are along the lines of, "If localization settings on the server are changed then this query would give unintended results that may not be identified in a timely manner and Bad Things could happen." Fair enough. But most of the applications I've worked on are not fully localized and in these cases I would imagine the case actually is, "If localization settings on the server are changed then many, many Bad Things will happen very quickly before anyone even has a chance to run this query."

In other words, failing to fully localize may well be an acceptable risk for an application that was designed to run in only one region.