Re: will this date formatting work?

"Analysis&Solutions" <info_at_analysisandsolutions.com> wrote in message news:bo3g8g$5jb$1_at_reader2.panix.com...
> In <9kJob.74309$HS4.643791_at_attbi_s01> "Jim Kennedy"
<kennedy-down_with_spammers_at_no_spam.comcast.net> writes:
>
> >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 have a point about attempts to be independent causing problems. Do
> realize, though, the real issue I'm trying to discuss isn't about date
> "formats," it's about the SQL standard saying DATE fields store a _date_,
> but Oracle's DATE fields include the time too. The date/time combination
> is known as a TIMESTAMP field in the SQL standards. I suppose Oracle
> doesn't have a TIMESTAMP field.
>
> Hmmm... many databases do silent data type conversions for certain
> situations in CREATE TABLE statements. Does Oracle do that? For example,
> if the following query were sent to Oracle...
>
> CREATE TABLE STP_SystemDate (
> SystemDate TIMESTAMP NOT NULL
> );
>
> would it change the TIMESTAMP to a DATE automatically? Or would it die
> and generate an error message?
>
>
> >You should be able to do:
> >CREATE TABLE STP_SystemDate (
> > SystemDate date default 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.
>
> Good thinking, but SYSDATE is an Oracleism.
So what. Every database has their isms. I am more interested in getting work done in a reasonable about of time not screwing around with making things "database independent" I've worked in places with multiple backends and being "database independent" just makes things slow and unwiedly.

>
> Displaying the date isn't as important as my making sure the dates and
> times are being saved correctly. Hence I've repeatedly asked the question
> I hope someone will answer: what would happen if a query inserts a date
> formatted as "2003-10-30" into an Oracle DATE field.
You could try it. It depends upon what nls_date_format is. If the format happens to be specified as yyyy-mm-dd then it will work and the time component will be as of midnight.(0 time) That is why to_date is good, because there is no ambiguity as to what you mean.

>
> 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 Sun Nov 02 2003 - 12:57:38 CST