sysdate displaying date and time 1

[slok]

how can I retrieve a sysdate value and display it in date and time?

I have a simple table "test" with a field datetime and datatype date.

I do an insert as follows:

==
insert into test values (sysdate);
==

now, I want to retrieve the value and use it to insert into
another table "testB"

But "testB" needs to show that the value is both date_time
value.

How can I do that.

 

[sem]

In fact Oracle stores in DATE field both date and time. You may show the time part or not by using format masks. The default one usually does not contain time part, e.g. 'DD-MON-YYYY' but you're in power to change this by applying another, say 'dd.mm.rr hh24:mi:ss' using it in explicit TO_CHAR() conversion or changing it on a session level by ALTER SESSION.
So INSERT INTO.. SELECT... will make a thing.

 

[Turkbear]

Just to clarify:

All Oracle DATE type fields are DateTime and are stored as a number ( the # of days since some date which I forget ) and, if not specified when input, the time is 00:00:00.

To insert a date from TableA to TableB
just a

'insert into TableB(DateField) select DateField from TableA'

will do..

To Create a Date field ( with a Time specified ) you need to use the To_Date('DateTimeString','FormatString') function like:

insert into TableA(DateField) values
(to_date('20020202 11:00','YYYYMMDD HH24:Mi'))

which will enter 2/2/2002 11:00am as the value of the Date field


To DISPLAY the Date in some specified format, use the To_Char(DateField,'FORMAT MASK') function..This does not, in any way, change the actual value of the date field, only how you see it....
So, for example to view the date we entered above
as 02/02/2002 use

Select to_char(DateField,'MM/DD/YYYY') from TableA

hth

 

[sem]

Turkbear,

Just to clarify:
Oracle does not use that format. Instead of this it uses 8 byte field.

bytes meaning
----- -------
1-2 year
3 month
4 day
5 hour
6 minute
7 second
8 0 (reserved?)

So you've probably forgotten the platform rather than day

 

[MikeLacey]

sem Hi,

That's interesting -- where did you pick that up from? Mike
______________________________________________________________________
"Experience is the comb that Nature gives us after we are bald."

Is that a haiku?
I never could get the hang
of writing those things.

 

[sem]

Select dump(sysdate) from dual
/