0

I have to put in STIMING a time when I insert I use TO_DATE function but it give me date not time and it should be time.

This is the table and the code that i use

SQL> select * from shift;

       SNO SNAME                STIMING
---------- -------------------- ---------
    121323 morning              01-APR-17
    112232 evening              01-APR-17
    665342 afternoon            01-APR-17

SQL> update shift
2  set  STIMING= ('07:00:00 HH,MI,SS') 
3  where SNO=121323;
set  STIMING= ('07:00:00 HH,MI,SS')             
 *
ERROR at line 2:
ORA-01843: not a valid month
MT0
  • 143,790
  • 11
  • 59
  • 117
narjis
  • 1
  • Add to question table description, so that people can answer you. – Seyran Apr 23 '17 at 12:49
  • You must provide a DATE component for the `DATE` [data type](https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT413). Select an arbitrary date, eg 2000-01-01, and store all values as time on 2000-01-01, eg `TO_DATE('2000-01-01 07:00:00', 'YYYY-MM-DD HH24:MI:SS')`. – Serge Apr 23 '17 at 12:51

2 Answers2

1

I have to put in STIMING a time

Oracle does not have a TIME datatype. The DATE data type is always stored internally as 7-bytes and is always composed of year (2-bytes) and month, day, hours, minutes and seconds (1-byte each).

You cannot not have a year, month or day component of a DATE.

If you want a time on its own then you will have to store it as a different data type or store the year/month/day and ignore that component.

When you are SELECTing the STIMING column it is not showing the time component. You can change this by changing the default date format which is set in the NLS_DATE_FORMAT session parameter.

You can review this parameter using:

SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

You can set this value within your current session using:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

(Note: this does not change the value for any other users.)

When you insert the date you can use:

INSERT INTO shift ( SNO, SNAME, STIMING)
VALUES ( 121323, 'morning', TO_DATE( '01-APR-2017 07:00' DD-MON-YYYY HH24:MI' ) )

Or, an ANSI TIMESTAMP literal (which will be implicitly cast to the DATE format of the column):

INSERT INTO shift ( SNO, SNAME, STIMING)
VALUES ( 121323, 'morning', TIMESTAMP '2017-04-01 07:00:00' )
MT0
  • 143,790
  • 11
  • 59
  • 117
0

I suggest you to avoid updates, change your insert part from to_date with no formatting param to to_date( colname, 'DD-MON-YY HH24:MI:SS')

Seyran
  • 711
  • 4
  • 8