Re: will this date formatting work?

In <bnu2rf$meq$1_at_news2.tilbu1.nb.home.nl> Frank <fvanbortel_at_netscape.net> writes:

>Date formatting is about FORMATTING, not about dates.

Yes, I understand.

>CREATE TABLE STP_SystemDate (
> SystemDate date default to_date('0001-01-01','YYYY-MM-DD') NOT NULL
> );

But that will fail on most other DBMS's. What would Oracle do if I did this...

CREATE TABLE STP_SystemDate (
  SystemDate date default '0001-01-01' NOT NULL );

Would it accept it without comment? Would it accept it, but complain? Would it reject it and raise an error? Would the behavior be different if before issuing the CREATE TABLE statement I did

   ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
>Internally, a date is just a number (usually the number of seconds
>since a certain date/time).

Yes, I understand.

>Keeping that in mind, just do:
>UPDATE STP_SystemDate SET SystemDate = sysdate;

Thing is, the SystemDate doesn't equal the sysdate. The data I'm dealing with continues to deal with yesterday's data until around 6am.

So, what would happen if I execute this query?

   UPDATE STP_SystemDate SET SystemDate = '2003-10-30';

Would the behavior be any different if I had already put in:

   ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
>Then, select it again:
>SELECT SystemDate FROM STP_SystemDate;
>and if you would like a different format, apply it:
>SELECT to_char(SystemDate,'YYYY-MM-DD') FROM STP_SystemDate;

Similarly, to_char() is an Oracleism. Most DBMS's will choke on that.

>This formatting is a good idea anyway - you never know what the
>local defaults are (e.g. US like MM/DD/YY, Europe DD/MM/YY)

Which is the purpose of the SET NLS_DATE_FORMAT query, correct?

Thanks,

--Dan

-- 
     FREE scripts that make web and database programming easier
           http://www.analysisandsolutions.com/software/
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
 4015 7th Ave #4AJ, Brooklyn NY    v: 718-854-0335   f: 718-854-0409