-
to_date problems
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.
-
Is this what you want...
Code:
SQL>SELECT to_char(sysdate,'dd Mon YYYY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DDM
--------------------
19 Mar 2003 15:46:17
-
alternatively you can ...
alter session set nls_date_format='dd Mon YYYY HH24:MI:SS';
... which will change your session default date format.
-
hmm
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
-
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 "").
-
aha
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.