Re: Much trouble working with Dates

From: Alan <alanshein_at_erols.com>
Date: Fri, 22 Mar 2002 14:59:17 -0500
Message-ID: <a7g28r$l3ob1$1@ID-114862.news.dfncis.de>

Oracle always stores the date the same way, no matter how you enter it. You need to format it again upon retrival using TO_CHAR:

SQL> SELECT SYSDATE FROM DUAL; SYSDATE

---

22-MAR-02 SQL> SELECT TO_CHAR(SYSDATE,'MM-DD-YYY hh:mi:ssAM')   2 FROM DUAL; TO_CHAR(SYSDATE,'MM-DD-YYYHH:MI:SSAM')

---

03-22-002 02:52:14PM

 but I suspect you really want:

SQL> 1 SELECT TO_CHAR(SYSDATE,'MM-DD-YYYY hh:mi:ssAM')   2* FROM DUAL
SQL> / TO_CHAR(SYSDATE,'MM-DD-YYYYHH:MI:SSAM')

---

03-22-2002 02:52:28PM

or maybe even

  1 SELECT TO_CHAR(SYSDATE,'MM-DD-YYYY hh:mi:ss AM')   2* FROM DUAL
SQL> / TO_CHAR(SYSDATE,'MM-DD-YYYYHH:MI:SSAM')

---

03-22-2002 02:53:46 PM

"chiranjp" <chiranjp_at_yahoo.com> wrote in message news:7b142cd4.0203221127.4a6d5740_at_posting.google.com...
> 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.
Received on Fri Mar 22 2002 - 13:59:17 CST