ORACLE insert Time and Day in DATE fields.

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 ...

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

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

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

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

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

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

[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..