Hi,

I just learnt this SQL language and I'm a student. Could you please tell me how do you insert time into the table? I know you can use TO_DATE('dd/mm/yyyy' , 'xx/xx/xxxx') to insert dates into the table, but how do you insert time e.g. 10.00AM??? I already tried using TO_DATE('HH:MI AM', 'xx:xx AM/PM') but it doesn't work!!!

Thanks in advance...!

Which SQL server are you using?

hmm I dont know, I am actually connected to my university database and I'm using Oracle SQL*Plus 8.0!

I just need to create table using CREATE TABLE <table name> ..... and insert all the datas required for my assignment using INSERT INTO <table name>......., and I got problems when inserting time into the tables, when I'm using for example: INSERT INTO flight (departuretime) VALUES (TO_DATE('10:00 PM', 'HH:MI AM'));

but when I inserted the data above, when I looked into the table using SELECT*FROM FLIGHT; it shown 02-SEP-02 instead of the time I inserted....., I dont know what's wrong with that statement...!!???

You should use the default date type, which is sysdate for Oracle.

Example:

INSERT INTO tbl_something (Date) VALUES (sysdate)

In the table create statement, you would use the following:

COLUMNNAME DATE DEFAULT(sysdate) NOT NULL
========================================
Buh Bye!

Cheers,
Me

stealth:

I haven't try your suggestion, but I just tried inserting particular date and time such as TO_DATE('26-DEC-02 11:00 AM', 'DD-MON-YY HH:MI AM'), it did insert the correct date, however the time always set @ 12:00 AM...! why???

I use this procedure to extract the date (when I type SELECT*FROM flight, the depttime display the date only not incl the time, so I use this procedure below to get the time)

DECLARE
Time VARCHAR2(15);
Time2 VARCHAR2(15);
DEPTIME VARCHAR2(15);
BEGIN

SELECT f.DEPARTURETIME
INTO TIME
FROM FLIGHT f
WHERE FLIGHTNUMBER = 500006;

Time2 := TO_CHAR(TO_DATE(Time, 'DD-MON-YY'), 'HH:MI AM');
DBMS_OUTPUT.PUT_LINE(Time2);
DBMS_OUTPUT.PUT_LINE(Time);
END;
/

Time2 always 12:00AM, no matter what ......!!??!!! I tried inserting different time, but still no go....

any ideas??

One flaw in your codes is that you are declaring Time2 as a VARCHAR column type rather than DATE. That is probably the crux of your problem. But if all you are doing is inserting the present time into the table, then using sysdate for a DATE column, will insert the current date and time. How about trying my original suggestion?
========================================
Buh Bye!

Cheers,
Me

Hello,

I am trying to insert date and time to my table , but when I do a select for the column , it only returns the date but the time is not shown.

Below is my code for INSERT :

INSERT INTO appointment
VALUES (TO_DATE('NOV 5 1997','MON DD ,YYYY'),TO_DATE('01/27/2000 03:19:00 PM','MM/DD/YYYY HH:MI:SS PM'),'MARK','RAJU');


Table appointment property :

app_date date NOT NULL,
app_time date NOT NULL,
patient_name VARCHAR(20) NOT NULL,
doctor_name VARCHAR(20) NOT NULL,

I am using Oracle 8i.



Thanks



Andy

Hi,

If insert goes error free and it is during select that you are not getting what you want then try the following:

select to_char(app_time,'HH:MI:SS AM') from appointment

Following Will Be The O/P

TO_CHAR(APP_TIME,'MM/DD/YYYYHH:MI:SSAM')
-------------------------------------------------------
01/27/2000 03:19:00 PM

Hope This Solves Your Problem

I Personally Feel You Dont Need An Extra Column For App_time U can save this Information In Your app_date Col And USe The To_Char Function For Date Datatype TO get the required Information related to time

We Are Coming With A Site Go4Oracle.com To Deal With Such Issue With Oracle Database (SQL/PLSQL) And Developer 2000 as front end tool for all application programmer

THnx