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 Go to next message
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 #568593 is a reply to message #568589] Mon, 15 October 2012 00:05 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that you are missing single quotes around 2012-10-15, i.e. should be
to_char('2012-10-15', 'yyyy-mm-dd')
Re: How to fetch value which column has timestamp datatype? [message #568595 is a reply to message #568593] Mon, 15 October 2012 00:10 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi sir,

i used single quotes and getting this error.

SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:

thanks
Re: How to fetch value which column has timestamp datatype? [message #568599 is a reply to message #568595] Mon, 15 October 2012 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: How to fetch value which column has timestamp datatype? [message #568601 is a reply to message #568599] Mon, 15 October 2012 00:54 Go to previous messageGo to next message
Littlefoot
Messages: 18833
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 #568602 is a reply to message #568601] Mon, 15 October 2012 01:00 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi sir,

getting the same error:

Error report:
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
my both table has the same datatype in all columns.

thanks
Re: How to fetch value which column has timestamp datatype? [message #568603 is a reply to message #568602] Mon, 15 October 2012 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PDate = to_char(2012-10-15,'yyyy-MM-dd')


This is silly because:
1/ 2012-10-15 is 1987 so a number can't have a format like 'yyyy-MM-dd'.
2/ If you missed the quote, like Littlefoot suggested, then it is a string and so to_char on a string...

If PDate is of DATE datatype then the correct condition is:

PDate = TO_DATE ('2012-10-15', 'yyyy-MM-dd')


Regards
Michel
Re: How to fetch value which column has timestamp datatype? [message #568604 is a reply to message #568602] Mon, 15 October 2012 01:07 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you, please, post CREATE TABLE statements for both tables, as well as sample INSERT INTO statement for a few records that belong to PROCESSDAILYDATA, its EMP_ID = '00000001' and PDATE = 15th Oct 2012?

P.S. I just saw Michel's message.

./fa/450/0/ Good morning! Partial blindness on my side, sorry.

[Updated on: Mon, 15 October 2012 01:09]

Report message to a moderator

Re: How to fetch value which column has timestamp datatype? [message #568605 is a reply to message #568603] Mon, 15 October 2012 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And as I said and repeated in your 3 or 4 last topics about dates, you should REALLY read the documentation we pointed you in these topics.

There are 3 points to read (links in your previous topics):
- TO_DATE (to convert a string to a date)
- TO_CHAR (to convert a date to a string)
- Date and time format elements

Regards
Michel
Re: How to fetch value which column has timestamp datatype? [message #568606 is a reply to message #568604] Mon, 15 October 2012 01:10 Go to previous messageGo to next message
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 #568607 is a reply to message #568606] Mon, 15 October 2012 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: How to fetch value which column has timestamp datatype? [message #568608 is a reply to message #568607] Mon, 15 October 2012 01:27 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Try like..

INSERT INTO shiftsample (Empid, PPDate, Inpunch, outpunch)  
SELECT Emp_ID, PDate, In_Punch, Out_Punch FROM ProcessDailyData 
WHERE trunc(PDate) = to_date( '2012/10/13','yyyy/MM/dd') AND Emp_ID = '00000003'
Re: How to fetch value which column has timestamp datatype? [message #568609 is a reply to message #568608] Mon, 15 October 2012 01:36 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi sir i run this query

and now getting different error that is:

Error at Command Line:16 Column:0
Error report:
SQL Error: No more data to read from socket

thanks
Re: How to fetch value which column has timestamp datatype? [message #568610 is a reply to message #568609] Mon, 15 October 2012 01:37 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Reconnect the database and try
Re: How to fetch value which column has timestamp datatype? [message #568631 is a reply to message #568610] Mon, 15 October 2012 06:06 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi getting the same error after restart my database

Error at Command Line:19 Column:0
Error report:
SQL Error: No more data to read from socket

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 Go to previous messageGo to next message
Littlefoot
Messages: 18833
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 #568635 is a reply to message #568634] Mon, 15 October 2012 06:22 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi sir,

AMPLEX_GRAND1 this i created user and i have given all priviliges to him.
so when i export table data so it's shown like that.

thanks
Re: How to fetch value which column has timestamp datatype? [message #568636 is a reply to message #568635] Mon, 15 October 2012 06:25 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does it mean that both SHIFTSAMPLE and PROCESSDAILYDATA tables belong to AMPLEX_GRAND1?
Re: How to fetch value which column has timestamp datatype? [message #568637 is a reply to message #568636] Mon, 15 October 2012 06:26 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
yes sir you are right.

Re: How to fetch value which column has timestamp datatype? [message #568638 is a reply to message #568637] Mon, 15 October 2012 06:30 Go to previous message
Littlefoot
Messages: 18833
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';
Previous Topic: how to write the Query to convert the XML to table
Next Topic: How to join two where?
Goto Forum:
  


Current Time: Sat Apr 19 19:05:46 CDT 2014

Total time taken to generate the page: 0.10080 seconds