Re: Date format columns

From: Mark D Powell <mark.powell_at_eds.com>
Date: 8 Nov 2001 06:54:23 -0800
Message-ID: <178d2795.0111080654.58e92450@posting.google.com>

yavior_at_mercury.co.il (Yaron Avior) wrote in message news:<1b00d968.0111080152.5f0ca111_at_posting.google.com>...
> Hi,
> I want to use an american date format (mm/dd/yy) when inserting data
> into tables. i.e : INSERT INTO TABLE (X,Y,DATE)
> VALUES (9,'DDD','11/24/01').
> I receive the following error : "ORA-01843: not a valid month",
> although I have a trigger on my db : TRIGGER TR_LOGON
> AFTER LOGON ON DATABASE
> BEGIN
> EXECUTE IMMEDIATE('ALTER SESSION SET NLS_DATE_FORMAT=MM/DD/YY');
> END;
>
> what should I do to be able using queries as described ?
>
> thanks

If you execute your sql statement above in sqlplus it should error off as you are missing single quotes around the date format which must appear like 'mm/dd/yy' within the quoted string:

UT1> select sysdate from dual;

SYSDATE

08-NOV-01

UT1> @tdate
UT1> set serveroutput on
UT1> --
UT1> --
UT1> begin

  2 --
  3 execute immediate('alter session set nls_date_format = ''mm/dd/yy''');
  4 end;
  5 /

PL/SQL procedure successfully completed.

UT1> select sysdate from dual
2 /

SYSDATE

11/08/01

Mark D Powell --

Received on Thu Nov 08 2001 - 08:54:23 CST