Re: will this date formatting work?

Different DBMS deal with date FORMATS differently. Live with it; it is a fact. By trying to be "database independent" you are going to shoot yourself in the foot big time.
You should be able to do:
CREATE TABLE STP_SystemDate (

    SystemDate date default sysdate NOT NULL   );

which is a date. You might even be able to do: (I am not at work so I can't try it, but you could do it in a trigger) CREATE TABLE STP_SystemDate (

    SystemDate date default trunc(sysdate) NOT NULL   );

Then you are NOT dealing with date formatting just the date as a date datatype. You can choose to display it in your application however you would like to; it is irrelevant to the back end.

Jim

"Analysis&Solutions" <info_at_analysisandsolutions.com> wrote in message news:bnu62g$icu$1_at_reader2.panix.com...
> 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
Received on Sat Nov 01 2003 - 01:32:21 CST