Re: SQL Server Yukon

info_at_analysisandsolutions.com (Analysis&Solutions) wrote in message news:<bq607k$2l7$1_at_reader2.panix.com>...
> Greetings:
>
> I posted a question a month ago about using a particular date format to
> get data into and out of an Oracle database.
>
> While I appreciate everyone's sincere efforts to help, y'all seemed to
> reply with tips on how to do things "the Oracle way" rather than answering
> the question. For example, replies digressed into the concepts of how the
> data is stored, just as had happend to all prior threads on this topic I
> saw in the newsgroup archives. Others suggested using different formats.
> Oh well...
>
> So, I finally got around to downloading a development version of 9i and
> running a test. I figured it's a good idea to post a followup for the
> benefit of those in the group now and those searching the archives in the
> future.
>
> My question was, in essence, "will the following work?"
>
>
> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
> CREATE TABLE STP_SystemDate (
> SystemDate date default '0001-01-01' NOT NULL
> );
> INSERT INTO STP_SystemDate VALUES (DEFAULT);
> INSERT INTO STP_SystemDate VALUES ('2003-10-30');
> SELECT SystemDate FROM STP_SystemDate;
>
>
> The output from the above is...
>
> SYSTEMDATE
> ----------
> 0001-01-01
> 2003-10-30
>
> So, the answer I was looking for is, "yes."
>
> Again, thank you for your tips,
>
> --Dan

Yes, you can do it but it is a BAD idea:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; SQL> INSERT INTO STP_SystemDate VALUES (DEFAULT); INSERT INTO STP_SystemDate VALUES (DEFAULT) *
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month

The correct way to define the column is to use a DATE value for the default not a VARCHAR2 value:

CREATE TABLE STP_SystemDate (
  SystemDate date default to_date('0001-01-01','yyyy-mm-dd') NOT NULL   ); Received on Fri Nov 28 2003 - 06:13:01 CST