to_date problems

[Poot]

Hello, I am quite new to SQL and am having some problems. I have populated a table with info as well including the date and time... though when I go to search for the date and time... I only seem to be able to access the date! Can anyone point me in the right direction?

Below is what I have done:

I created table1 as such -

CREATE TABLE table1(
person INTEGER
,whn DATE
,code VARCHAR(10)
,PRIMARY KEY (person,whn)
);

And populated the table with -

INSERT INTO table1 VALUES (1,TO_DATE('25 Feb 2003 06:10:00','dd Mon YYYY HH24:MI:SS'),'XE 0A DB');

---- though when attempting -

SELECT whn FROM table1

I only get back the date ---- 25-FEB-03?!?

Thank you for any reply!

Regards,
Poot.

[jovery]

Is this what you want...


SQL>SELECT to_char(sysdate,'dd Mon YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DDM
--------------------
19 Mar 2003 15:46:17

[slimdave]

alternatively you can ...

alter session set nls_date_format='dd Mon YYYY HH24:MI:SS';

... which will change your session default date format.

[Poot]

Hello, thank you for your replies.

I think I see where you are going, but I actually want to put the time in manually rather than the system clock - and having done this I am finding it hard to retrieve...

any ideas...

Regards again... and thank you for any reply

Poot

[DaPi]

Hi poot,

Populating the column with data as you did in your first post INSERT is spot on.

Oracle stores both date and time in a DATE column
..... BUT .....
(by default) it only displays the date in a SELECT unless you tell it to do otherwise. Which is what jovery & slimdave are saying (the use of sysdate & dual was just for an example - you should replace them with "whn" and "table1" - without the "").

[Poot]

Thnx for the last post. I see whats what now! I think I really need to get a large book though as I have many more daft questions that need answering!

Thank you all for your time though.

Regards,
Poot.