Home » SQL & PL/SQL » SQL & PL/SQL » How to fetch value which column has timestamp datatype? (11g)
| How to fetch value which column has timestamp datatype? [message #568589] |
Sun, 14 October 2012 23:46  |
 |
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
|
Member |
|
|
Hi all,
Hope doing well,
sir i am running one query which is here
INSERT INTO shiftsample (Empid, PPDate, Inpunch, outpunch)( SELECT Emp_ID, PDate, In_Punch, Out_Punch FROM ProcessDailyData WHERE PDate = to_char(2012-10-15,'yyyy-MM-dd') AND Emp_ID = '00000001' );
in this query pdate has timestamp datatype and in shiftsample ppdate column has date type.
so i am not able to insert value from processdailydata table.
getting this error.
SQL Error: ORA-01481: invalid number format model
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.
thanks
|
|
|
|
|
|
|
|
|
|
| Re: How to fetch value which column has timestamp datatype? [message #568601 is a reply to message #568599] |
Mon, 15 October 2012 00:54  |
 |
Littlefoot
Messages: 16995
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I formatted your code so now it looks like this (which is much easier to read); of course, you promise that you'll do that yourself next time you post some code (here's a short tutorial which will take only a few seconds of your time):
INSERT INTO shiftsample (Empid,
PPDate,
Inpunch,
outpunch)
(SELECT Emp_ID,
PDate,
In_Punch,
Out_Punch
FROM ProcessDailyData
WHERE PDate = TO_CHAR ('2012 - 10 - 15', 'yyyy-MM-dd')
AND Emp_ID = '00000001');
Compare datatypes of columns involved in this statement in both "processdailydata" and "shiftsample" tables. What is the result?
[Updated on: Mon, 15 October 2012 00:55] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: How to fetch value which column has timestamp datatype? [message #568606 is a reply to message #568604] |
Mon, 15 October 2012 01:10 |
|
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
|
Member |
|
|
Here is my shiftsample table script which is empty
CREATE TABLE "AMPLEX_GRAND1"."SHIFTSAMPLE"
( "ID" NUMBER(10,0),
"EMPID" VARCHAR2(50 BYTE),
"PPDATE" TIMESTAMP (6),
"INPUNCH" TIMESTAMP (6),
"OUTPUNCH" TIMESTAMP (6)
)
and here is my processdailydata table
CREATE TABLE "PROCESSDAILYDATA"
( "ID" NUMBER(10,0),
"EMP_ID" VARCHAR2(4000 BYTE),
"EMP_NAME" VARCHAR2(4000 BYTE),
"PDATE" TIMESTAMP (6),
"IN_PUNCH" TIMESTAMP (6),
"OUT_PUNCH" TIMESTAMP (6),
"STATUS" VARCHAR2(4000 BYTE),
"WORKHRS" VARCHAR2(4000 BYTE),
"TOTAL_HRS" NUMBER(10,0),
"LATEBY" VARCHAR2(4000 BYTE),
"EARLYBY" VARCHAR2(4000 BYTE),
"OT_START" VARCHAR2(4000 BYTE),
"OT" VARCHAR2(4000 BYTE),
"SHIFT_IN" VARCHAR2(4000 BYTE),
"SHIFT_OUT" VARCHAR2(4000 BYTE),
"SHIFT_CODE" VARCHAR2(4000 BYTE),
"PRESENT" VARCHAR2(4000 BYTE),
"ABSENT" VARCHAR2(4000 BYTE),
"HOLIDAY" VARCHAR2(4000 BYTE),
"LEAVE" VARCHAR2(4000 BYTE),
"TOTAL_OVERTIME" VARCHAR2(4000 BYTE),
"COMP_NAME" VARCHAR2(4000 BYTE),
"DEPT_NAME" VARCHAR2(4000 BYTE),
"DESIG_NAME" VARCHAR2(4000 BYTE),
"CAT_NAME" VARCHAR2(4000 BYTE),
"BREAKIN" VARCHAR2(4000 BYTE),
"BREAKOUT" VARCHAR2(4000 BYTE),
"BREAKHRS" VARCHAR2(4000 BYTE),
"TOTALHRS_VARCHAR" VARCHAR2(4000 BYTE),
"SHIFT_NAME" VARCHAR2(20 BYTE),
"CONTACT" VARCHAR2(20 BYTE),
"DOJ" TIMESTAMP (6)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
REM INSERTING into AMPLEX_GRAND1.PROCESSDAILYDATA
SET DEFINE OFF;
Insert into AMPLEX_GRAND1.PROCESSDAILYDATA (ID,EMP_ID,EMP_NAME,PDATE,IN_PUNCH,OUT_PUNCH,STATUS,WORKHRS,TOTAL_HRS,LATEBY,EARLYBY,OT_START,OT,SHIFT_IN,SHIFT_OUT,SHIFT_CODE,PRESEN T,ABSENT,HOLIDAY,LEAVE,TOTAL_OVERTIME,COMP_NAME,DEPT_NAME,DESIG_NAME,CAT_NAME,BREAKIN,BREAKOUT,BREAKHRS,TOTALHRS_VARCHAR,SHIFT_NAME,C ONTACT,DOJ) values (1,'00000003','Madhusudhan k',to_timestamp('13-OCT-12 06.12.30.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'),null,null,'A',null,null,null,null,null,null,'Jan 1 1900 6:00AM','Jan 1 1900 2:00AM','SN00',null,null,null,null,null,'Sony','Card Printing','Software Engineer','IT',null,null,null,null,'Night Shift','9591874512',null);
Insert into AMPLEX_GRAND1.PROCESSDAILYDATA (ID,EMP_ID,EMP_NAME,PDATE,IN_PUNCH,OUT_PUNCH,STATUS,WORKHRS,TOTAL_HRS,LATEBY,EARLYBY,OT_START,OT,SHIFT_IN,SHIFT_OUT,SHIFT_CODE,PRESEN T,ABSENT,HOLIDAY,LEAVE,TOTAL_OVERTIME,COMP_NAME,DEPT_NAME,DESIG_NAME,CAT_NAME,BREAKIN,BREAKOUT,BREAKHRS,TOTALHRS_VARCHAR,SHIFT_NAME,C ONTACT,DOJ) values (21,'00000001','Satya Narayana M V',to_timestamp('15-OCT-12 10.02.29.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('15-OCT-12 10.02.29.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('15-OCT-12 10.02.29.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),'P','09:00:00',null,null,null,null,null,null,null,'SG00',null,null,null,null,null,'I Card Solutions','Software Development','Director','Accounts',null,null,null,null,'General Shift','9568546584',to_timestamp('15-OCT-12 10.02.29.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));
thanks
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to fetch value which column has timestamp datatype? [message #568634 is a reply to message #568631] |
Mon, 15 October 2012 06:20 |
|
Littlefoot
Messages: 16995
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Well, it seems to be OK on my 10g (don't have 11g here):
SQL> select emp_id empid, pdate ppdate, in_punch inpunch, out_punch outpunch
2 from processdailydata ;
EMPID PPDATE INPUNCH OUTPUNCH
---------- ------------------------- ------------------------- -------------------------
00000003 13.10.12 18:12:30,000000
00000001 15.10.12 10:02:29,000000 15.10.12 10:02:29,000000 15.10.12 10:02:29,000000
SQL> insert into shiftsample (empid,
2 ppdate,
3 inpunch,
4 outpunch)
5 select emp_id,
6 pdate,
7 in_punch,
8 out_punch
9 from processdailydata
10 where trunc (pdate) = to_date ('2012/10/13', 'yyyy/MM/dd')
11 and emp_id = '00000003';
1 row created.
SQL> select * from shiftsample;
ID EMPID PPDATE INPUNCH OUTPUNCH
---------- ---------- ------------------------- ------------------------- -------------------------
00000003 13.10.12 18:12:30,000000
SQL>
SQL> insert into shiftsample (empid,
2 ppdate,
3 inpunch,
4 outpunch)
5 select emp_id,
6 pdate,
7 in_punch,
8 out_punch
9 from processdailydata
10 where trunc (pdate) = to_date ('2012/10/15', 'yyyy/MM/dd')
11 and emp_id = '00000001'
12 ;
1 row created.
SQL> select * from shiftsample;
ID EMPID PPDATE INPUNCH OUTPUNCH
---------- ---------- ------------------------- ------------------------- -------------------------
00000003 13.10.12 18:12:30,000000
00000001 15.10.12 10:02:29,000000 15.10.12 10:02:29,000000 15.10.12 10:02:29,000000
SQL>
No problem at all. Note that all objects are in my own schema. You mention "AMPLEX_GRAND1" - what is it? Another schema? If so, where is it? In your own database or another one? Do you have privileges to write to that owner's tables?
|
|
|
|
|
|
|
|
|
|
| Re: How to fetch value which column has timestamp datatype? [message #568638 is a reply to message #568637] |
Mon, 15 October 2012 06:30 |
|
Littlefoot
Messages: 16995
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Run the following statements in SQL*Plus and copy/paste the result back here:
SQL> col empid format a10
SQL> col ppdate format a25
SQL> col inpunch format a25
SQL> col outpunch format a25
SQL>
SQL> select emp_id empid, pdate ppdate, in_punch inpunch, out_punch outpunch
2 from processdailydata
3 where emp_id = '00000001';
|
|
|
|
Goto Forum:
Current Time: Fri May 24 10:50:40 CDT 2013
Total time taken to generate the page: 0.31116 seconds
|
] 
Members
Search
Help
Register
Login
Home
