Inserting into Oracle"s timestamp type

Hi Folks

I see that if I have

create table history (history_timestamp timestamp not null)

I can insert into this with

insert into history (history_timestamp) values
(to_date('2005-12-02', 'YYYY-MM-DD') || ' 03:39:25PM')

(there's a space before the 03)

But what if my original date-time is a single string with a 24 hour clock,
as in '2005-12-02 15:39:25'?

Is there an Oracle function which will accept the latter string, or do I have to
construct the former monstrosity manually?

TIA.

--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html

Re: Inserting into Oracle"s timestamp type

On Thursday 01 December 2005 11:24 pm, Ron Savage wrote:
> Hi Folks
>
> I see that if I have
>
> create table history (history_timestamp timestamp not null)
>
> I can insert into this with
>
> insert into history (history_timestamp) values
> (to_date('2005-12-02', 'YYYY-MM-DD') || ' 03:39:25PM')

to_date('2005-12-02 03:39:25', 'YYYY-MM-DD HH:MI:SSAM')

You can access all of oracle's doc for free (just sign up), at oracle.com

Re: Inserting into Oracle"s timestamp type

On Thursday 01 December 2005 11:24 pm, Ron Savage wrote:
> Hi Folks
>
> I see that if I have
>
> create table history (history_timestamp timestamp not null)
>
> I can insert into this with
>
> insert into history (history_timestamp) values
> (to_date('2005-12-02', 'YYYY-MM-DD') || ' 03:39:25PM')

opps 24 hour clock like this:
to_date('2005-12-02 03:39:25', 'YYYY-MM-DD HH24:MI:SS')

RE: Inserting into Oracle"s timestamp type

This may not answer your question, but you can incorporate the time format
mask in the call to TO_DATE. For example ...

insert
into history (history_timestamp)
values (to_date(:ts_val, 'YYYY-MM-DD HH24:MI:SS'))

Steve

-----Original Message-----
From: Ron Savage [mailto:ron@savage.net.au]
Sent: Friday, 2 December 2005 4:25 PM
To: List - DBI users
Subject: Inserting into Oracle's timestamp type

Hi Folks

I see that if I have

create table history (history_timestamp timestamp not null)

I can insert into this with

insert into history (history_timestamp) values
(to_date('2005-12-02', 'YYYY-MM-DD') || ' 03:39:25PM')

(there's a space before the 03)

But what if my original date-time is a single string with a 24 hour clock,
as in '2005-12-02 15:39:25'?

Is there an Oracle function which will accept the latter string, or do I
have to
construct the former monstrosity manually?

TIA.

--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html

RE: Inserting into Oracle"s timestamp type

On Fri, 2 Dec 2005 17:50:08 +1100, Steve Baldwin wrote:

Hi Steve

Thanx.

> This may not answer your question, but you can incorporate the time
> format mask in the call to TO_DATE. For example ...
>
> insert
> into history (history_timestamp)
> values (to_date(:ts_val, 'YYYY-MM-DD HH24:MI:SS'))

Yes, I tried that first actually, but when I displayed the result via a=
select
in sqlplus all I saw was the date part.

However it now occurs to me that that display may have been truncated by=
sqlplus
since I did not use column history_timestamp format a30 or some such :-(.

And it's 6:00 pm Friday here now, and I don't feel like going back to work=
to
test it, for some reason...
--
Cheers
Ron Savage, ron@savage.net.au on 2/12/2005
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: Inserting into Oracle"s timestamp type

On Friday 02 December 2005 12:58 am, you wrote:
> On Fri, 2 Dec 2005 17:50:08 +1100, Steve Baldwin wrote:
>
> Hi Steve
>
> Thanx.
>
> > This may not answer your question, but you can incorporate the time
> > format mask in the call to TO_DATE. For example ...
> >
> > insert
> > into history (history_timestamp)
> > values (to_date(:ts_val, 'YYYY-MM-DD HH24:MI:SS'))
>
> Yes, I tried that first actually, but when I displayed the result via a
> select in sqlplus all I saw was the date part.
>
> However it now occurs to me that that display may have been truncated by
> sqlplus since I did not use column history_timestamp format a30 or some
> such :-(.
>
> And it's 6:00 pm Friday here now, and I don't feel like going back to work
> to test it, for some reason...

Ron,

It's not that sqlplus is truncating your output. The default format is
configured at database startup, which is overridable in various ways (
environmental variable NLS_DATE_FORMAT, alter session set NLS_DATE_FORMAT = ).
This format pertains to both selects and inserts.

If you want to select or insert with the precision you want, without changing
enviromentals or your session, you need to use to_char and to_date. So in
your case selecting would look like:

select to_char(date_col, 'YYYY-MM-DD HH24:MI:SS') from some_table;

and to insert would be:

insert into table (some_col) values (to_date(date_string, 'YYYY-MM-DD
HH24:MI:SS'));

HTH
Jay

Re: Inserting into Oracle"s timestamp type

> It's not that sqlplus is truncating your output. The default format is
> configured at database startup, which is overridable in various ways (
> environmental variable NLS_DATE_FORMAT, alter session set NLS_DATE_FORMAT = ).
> This format pertains to both selects and inserts.

He's using a timestamp column, not date, so substitute TIMESTAMP
for DATE in these suggestions.

SQL> create table fubar (d date, t timestamp);
SQL> insert into fubar values (sysdate, current_timestamp);
SQL> alter session set nls_date_format = 'yyyy-mm-dd';
SQL> select * from fubar;

D T
---------- --------------------------
2005-12-02 2005-12-02 14:16:31.356486


Note above that nls_date_format had no effect on timestamp column.



SQL>alter session set nls_timestamp_format = 'Day dd, Mon YYYY';

SQL> select * from fubar;

D T
---------- --------------------------
2005-12-02 Friday 02, Dec 2005


Mark

Re: Inserting into Oracle"s timestamp type

On Fri, 02 Dec 2005 14:18:49 -0800, Mark wrote:

Hi Folks

Thanx for all the suggestions. I have been reading Oracle's docs, but=
sometimes
it's a matter of not knowing what to look for, and sometimes it's a matter=
of
having been mislead by the manuals I have, which are printed by Thomson=
(Course
Technolgy) and which are ruthless in suppressing valuable detail.

--
Cheers
Ron Savage, ron@savage.net.au on 3/12/2005
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company