Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Much trouble working with Dates
Your statements to_date(v_logontime,'MM-DD-YYY hh:mi:ssAM') and
to_date(v_lastupdated,'MM-DD-YYY hh:mi:ssAM') are specifying the format
the date is in BEFORE you convert it. Oracle converts this into its own
internal date format. When you query it, it is up to the query tool to say
what format to present it in. If you would like to see it in this format
using Sql Plus then you will need to use the command:
ALTER SESSION SET NLS_DATE_FORMAT='MM-DD-YYY hh:mi:ssAM';
Then all dates from that session of sqlplus will appear in this format.
You probably have a similar option to use with DBA Studio
-- Ken Denny http://www.kendenny.com/ I feel a lot more like I do now than I did when I got up this morning. chiranjp_at_yahoo.com (chiranjp) wrote in news:7b142cd4.0203221127.4a6d5740_at_posting.google.com:Received on Fri Mar 22 2002 - 13:41:10 CST
> I am tryong to get a handle on Date values in Oracle 8i.
>
> I am using JDBC to set the session.getCretationTime() object via a
> Stored Procedure.
>
> Here's the procedure:
>
> create or replace procedure add_session_sp
> (v_sessionid varchar2,
> v_userid varchar2,
> v_logontime varchar2,
> v_lastupdated varchar2,
> v_ipaddress varchar2)
>
> AS
>
> BEGIN
>
> INSERT INTO sessions(
> sessionid,
> userid,
> logontime,
> lastupdated,
> ipaddress)
> VALUES(
> v_sessionid,
> v_userid,
> to_date(v_logontime,'MM-DD-YYY hh:mi:ssAM'),
> to_date(v_lastupdated,'MM-DD-YYY hh:mi:ssAM'),
> ipaddress);
>
> END;
>
>
> If I view the inserted record in the DBA Studio 'Table Data Editor',
> the date format is:
>
> March 22,2002 1:29:55 PM
>
> Obviosuly this is not the format I specified in the procedure.
>
> Using SQL Plus>select logontime from sessions:
>
> The result is 22-Mar-02
>
> This also confuses me. What happened to the formatting in both cases?
> I'm sure I've done something wrong because it would seem very dumb to
> have to explicitly provide the format everytime.