how about using this time format
'DD-MON-YYYY HH24:MI:SS'
"Frank" <fvanbortel_at_netscape.net> skrev i melding
news:bnu2rf$meq$1_at_news2.tilbu1.nb.home.nl...
> Analysis&Solutions wrote:
>
> > Hi Folks:
> >
> > I'm developing a PHP application that needs to operate under a wide
> > variety of DBMS's. I have no Oracle experience and recently learned
that
> > Oracle date columns store the entire date and time. My system has been
> > designed with the SQL-99 standard format of YYYY-MM-DD in mind. So, I
> > think I've come up with a plan to make things work. I'd love your
> > feedback, please...
> >
> > THE TABLE
> > ---------
> > CREATE TABLE STP_SystemDate (
> > SystemDate date default '0001-01-01' NOT NULL
> > );
> >
> >
> > WHEN MY APPLICATION STARTS, ISSUE THE FOLLOWING QUERY
> > -----------------------------------------------------
> > ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
> >
> >
> > DURING APPLICATION EXECUTION, THE FOLLOWING QUERIES WILL BE USED
> > ----------------------------------------------------------------
> > UPDATE STP_SystemDate SET SystemDate = '2003-10-30';
> >
> > SELECT SystemDate FROM STP_SystemDate;
> >
> > The result of this last query will be placed in one of my application's
> > variables, which can then be examined to find the value of "2003-10-30"
> >
> >
> > So, this will work, right?
> >
> > Thanks,
> >
> > --Dan
>
> Date formatting is about FORMATTING, not about dates.
> You put strings in dates, and all sorts of other
> weird stuff. I suspect your background is not Oracle...
>
> Just put in a date field, e.g.
> CREATE TABLE STP_SystemDate (
> SystemDate date default to_date('0001-01-01','YYYY-MM-DD') NOT NULL
> );
> I took the liberty of correcting your code; it will now be a date
> (to_date('0001-01-01','YYYY-MM-DD')) in a date column.
>
> Internally, a date is just a number (usually the number of seconds
> since a certain date/time). Keeping that in mind, just do:
> UPDATE STP_SystemDate SET SystemDate = sysdate;
>
> 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;
> SELECT to_char(SystemDate,'YYYY-MON-DD') FROM STP_SystemDate;
> SELECT to_char(SystemDate,'YYYY-Mon-dd HH:MI:SS') FROM STP_SystemDate;
> SELECT to_char(SystemDate,'MM/DD/YYYY') FROM STP_SystemDate;
> to name just a few.
>
> 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)
> --
> Regards, Frank van Bortel
>
Received on Sun Nov 02 2003 - 07:05:23 CST
