Re: PL/SQL time format Q

From: Lewis C <lewisc_at_excite.com>
Date: Mon, 11 Apr 2005 00:46:16 GMT
Message-ID: <avhj51d6g2p73r6btkhujj5afp4rplstc2@4ax.com>

On 10 Apr 2005 17:26:52 -0700, "melned" <uncle_melned_at_yahoo.com> wrote:

>I'm attempting to insert a julian time stamp into a table using PL/SQL.
>I'm able to alter my session such that SYSDATE output is julian in a
>select statement:
>
> alter session set NLS_DATE_FORMAT = 'J';
>
> Session altered.
>
> select SYSDATE from dual;
>
> SYSDATE
> -------
> 2453471
>
>
>When I attempt to insert this value, I end up with a 'DD-MMM-YY' format
>such as '10-APR-05'. The column into which I'm attempting to insert is
>of type DATE.
>
>
> alter session set NLS_DATE_FORMAT = 'J';
>
> INSERT into <table name>
> VALUES ( SYSDATE )
>
>
>Any pointers on where I've gone wrong would be greatly appreciated.

I would need to see the exact errors you're getting to really understand your question and what's happening but my personal preference is to always wrap the data in a to_date call, i.e.

INSERT INTO <table> (date_col)
VALUES (to_date('2453471', 'J'));

That way it doesn't matter what the NLS_DATE_FORMAT is set to.

But that's just me.

Lewis

Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database http://www.suite101.com/welcome.cfm/oracle

Received on Sun Apr 10 2005 - 19:46:16 CDT