PL/SQL time format Q

[melned]

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.

[Lewis C]

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

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

Sign up for courses here:
http://www.suite101.com/suiteu/default.cfm/416752
-----------------------------------------------------------

[IANAL_VISTA]

"melned" <uncle_...@yahoo.com> wrote in
news:1113179212.5...@o13g2000cwo.googlegroups.com:

impacts the output/display (ONLY) of DATE datatypes;

What exactly are you doing & what answer do you want?
What make you think you have a problem?

[Gump]

Yes, setting the NLS_DATE_FORMAT will affect display only.

You will need to do this...

insert into <table name>
values (to_char(SYSDATE,'J'))

Just be sure you have the correct datatype in the column you are
inserting to.

[Frank van Bortel]

Sigh... once more: dates are *stored* as NUMBERS.
only the display format changes with NLS settings changing,
or when using to_char with format masks.

Try to insert SYSDATE, and do a select from the same
table with your to_char(SYSDATE,'J') - it works, too!

Now, if you were storing this date into a number column,
that would be different... and a Really Bad Idea (tm).
--
Regards,
Frank van Bortel