asked on

ORACLE insert Time and Day in DATE fields.

I have an Oracle 9i v2 installation in my computer. I have 2 tables, table "SCHEDULE" and "ROUTES". I have a field in first table ("SCHEDULE") named "ATIME" and a field in second table named "ADAY", both of them as DATE. I need to input time in the ATIME field in 24h format and day in ADAY field in format DD-MM-YYYY.
However, I can't do that, because Oracle keeps asking for the missing date part (day, month and year for ATIME value and hour, minute, second in ADAY value). How on earth do I insert the values given to me into those fields?

N M

ASKER

I'm a newbie in Oracle; however, I enter the values in my database using the visual editor (while login to OMS as standalone) and I've noticed that I can see the SQL statement that inserts the new values in the table. If your answer involves additional user handling, please instruct me on how to do it (i.e. "go there, click that, choose X under Y and enter ABCDEF...")
MUCH appreciate, SORRY for inconvienience

Nick

jwahl

if your columns are defined as DATE, you always have to input a complete date string (day-month-year), optionally including the time (hour-minute-second).
when entering time only (with TO_DATE()), the time is stored within the first day of the current month.

some examples:

-- create table for testing:
VTB@ABOT> CREATE TABLE schedule (atime DATE);

Table created.

-- insert today's date including time:
VTB@ABOT> INSERT INTO schedule (atime) VALUES (SYSDATE);

1 row created.

VTB@ABOT> SELECT TO_CHAR(atime, 'DD.MM.YYYY HH24:MI:SS') FROM schedule;

TO_CHAR(ATIME,'DD.M
-------------------
22.02.2007 14:31:26

-- insert today's date without time:
VTB@ABOT> INSERT INTO schedule (atime) VALUES (TRUNC(SYSDATE));

1 row created.

VTB@ABOT> SELECT TO_CHAR(atime, 'DD.MM.YYYY HH24:MI:SS') FROM schedule;

TO_CHAR(ATIME,'DD.M
-------------------
22.02.2007 00:00:00

-- insert tomorrow's date including time:
VTB@ABOT> INSERT INTO schedule (atime) VALUES (TO_DATE('22-02-2007 11:30:00', 'DD-MM-YYYY HH24:MI:SS'));

1 row created.

VTB@ABOT> SELECT TO_CHAR(atime, 'DD.MM.YYYY HH24:MI:SS') FROM schedule;

TO_CHAR(ATIME,'DD.M
-------------------
22.02.2007 11:30:00

-- insert time only:
INSERT INTO schedule (atime) VALUES (TO_DATE('11:30', 'HH24:MI'));

1 row created.

VTB@ABOT> SELECT TO_CHAR(atime, 'DD.MM.YYYY HH24:MI:SS') FROM schedule;

hope this helps ...

jwahl

sorry - last part is missing:

VTB@ABOT> SELECT TO_CHAR(atime, 'DD.MM.YYYY HH24:MI:SS') FROM schedule;

TO_CHAR(ATIME,'DD.M
-------------------
01.02.2007 11:30:00

WallaceAdrian

We have a similar scenario, wanting to split the date and time. What we ended up doing was creating two columns:

event_date date;
event_time number(5);

The event time is stored as the number of seconds since midnight, so 10:15:23 equates to 36923.

To calculate seconds since midnight use the following

select to_char(to_date('22/02/2007 10:15:23', 'dd/mm/yyyy hh24:mi:ss'), 'SSSSS') from dual

To get the original date back use the following statement

select event_date + (event_time/86400) from dual; --86400 = number of seconds in a day

N M

ASKER

So, how do I enter a day like 13-JUN-2007 in field "ROUTES"."ADAY" and how do I enter time 13:45 in field "SCHEDULE"."ATIME" ?

I understood [jwahl] comment:
INSERT INTO schedule (atime) VALUES (SYSDATE);

also I can use insertion of time only:
INSERT INTO schedule (atime) VALUES (TO_DATE('11:30', 'HH24:MI'));

but how do I enter a date (say, 13-JUN-2007) ?

Also, for [WallaceAdrian] comment:
Your approach is interesting and I'm sure also correct, but unfortunately I don't have the right to change fields type (both my fields are DATE)

THANK you all

Nick

jwahl

> but how do I enter a date (say, 13-JUN-2007) ?

INSERT INTO schedule (atime) VALUES (TO_DATE('13-JUN-2007', 'DD-MON-YYYY'));

N M

ASKER

Hello,

Dear [jwahl] your comment
{ INSERT INTO schedule (atime) VALUES (TO_DATE('13-JUN-2007', 'DD-MON-YYYY')); }
is correct. Can you please provide me with something to enter the time (and time only)?

It will be a blessing if you could provide me a SELECT statement for both..

THANK YOU really

ASKER CERTIFIED SOLUTION

jwahl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

N M

ASKER

[jwahl]
THANK you for both statements, both correct and worked ok.
By SELECT statement I meant how do I select a date field based i.e. on hour, min and second..

jwahl

in select you have to use TO_CHAR, for conditions you have to use TO_DATE:

SELECT TO_CHAR(atime, 'DD.MM.YYYY HH24:MI:SS')
FROM schedule
WHERE atime BETWEEN TO_DATE('22.02.2007', 'DD.MM.YYYY') AND SYSDATE + 1;