Re: will this date formatting work?

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.

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.

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