Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: will this date formatting work?

Re: will this date formatting work?

From: Frank <fvanbortel_at_netscape.net>
Date: Fri, 31 Oct 2003 17:40:30 +0100
Message-ID: <bnu2rf$meq$1@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 Fri Oct 31 2003 - 10:40:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US