Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: type conversion from varchar2 to timestamp

    Hi,
    I get an error when i run the below query. It has something to do with type conversion but cant figure out where i'm wrong. I get the Invalid number error ORA-01722

    SELECT to_char(LAST_ACT,'DDMMYYYYHH24MI') FROM a_stg;

    Data in the table is as shown below -
    EMP_ID USER_ID LAST_ACT
    3261 abc 07/12/2011 17:16:48
    1577 def 08/13/2011 13:38:39
    1034 ghi 08/15/2011 15:14:28
    2798 jkl 08/13/2011 17:37:38


    Table description is given below -
    CREATE TABLE a_STG
    (
    EMP_ID VARCHAR2(2000),
    USER_ID VARCHAR2(2000) NOT NULL,
    LAST_ACT VARCHAR(30)
    )
    Last edited by nandinir; 08-23-11 at 19:09.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You do NOT TO_CHAR any VARCHAR2 datatype
    just as you don't TO_DATE any DATE datatype
    >to_char(a.last_login_timestamp,'DDMMYYYYHH24MI' )
    above is brain dead idiocy. It should be as below
    TO_TIMESTAMP(a.last_login_timestamp,'DDMMYYYYHH24M I'); the actual mask depends upon format of string data
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    to_timestamp conversion doesnt work either.

    SELECT to_timestamp(LAST_ACT_DSCR,'DDMMYYYYHH24MI') FROM A_STG

    ORA-01843: not a valid month

  4. #4
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    as an addendum to what anacedent posted, your date format string should correspond to the format of your data in the table:

    Code:
    SELECT to_char(LAST_ACT,'DDMMYYYYHH24MI') FROM a_stg;
    
    Data in the table is as shown below -
    EMP_ID USER_ID LAST_ACT
    3261 abc 07/12/2011 17:16:48
    1577 def 08/13/2011 13:38:39
    1034 ghi 08/15/2011 15:14:28
    2798 jkl 08/13/2011 17:37:38
    So, it might be a good idea to use the format mask 'MM/DD/YYYY HH24:MIS' (MI<colon>SS) instead of 'DDMMYYYYHH24MI' ...
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    The requirement is to insert data in DDMMYYYYHH24MI. I cant change the format mask.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The requirement is to insert data in DDMMYYYYHH24MI.
    DATE & TIMESTAMP datatypes do NOT have any inherent FORMAT!
    They are stored as internal BINARY values.
    FORMAT only exists as part of data presentation display.

    >I cant change the format mask.
    Yes, you can. You don't know, what you don't know!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2005
    Posts
    276
    I'm confused here now.
    Here's the thing - the field is defined as varchar2 on the table and I have to insert date in DDMMYYYYHH24MI format in the table.

    So I used to_timestamp to convert the date in mm/dd/yyyy hh:mm:ss format to DDMMYYYYHH24MI but thats not working.

    I tried to change the field to timestamp datatype but that didnt help either.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT LAST_ACT_DSCR FROM A_STG;

    post a handful of line produced by SQL above
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jul 2005
    Posts
    276
    This is what I get...
    LAST_ACT_DSCR

    07/12/2011 17:16:48
    08/13/2011 13:38:39
    08/15/2011 15:14:28
    08/13/2011 17:37:38
    08/04/2011 20:30:53
    08/12/2011 20:12:14
    08/15/2011 13:08:07
    06/30/2011 10:20:47
    08/10/2011 13:44:20
    08/13/2011 01:08:02

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by nandinir View Post
    The requirement is to insert data in DDMMYYYYHH24MI. I cant change the format mask.
    INSERT to what? There is no notice about INSERT in your previous post, only one SELECT statement and description of the source.

    INSERTing into the same table would not be a good idea at all, as the VARCHAR2 column contains values in different format - so, its content would be complete mess.

    If you are INSERTing into some other table, it would help if you posted its description as you did for A_STG table (CREATE TABLE command).

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Do NOT!!!!! store dates in a varchar2 field, it is very bad programming. Always store it in a date or timestamp field.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Jul 2005
    Posts
    276
    Here is the entire script and table definitions
    INSERT INTO final_tab
    (
    emp_id,
    user_id,
    last_act
    )
    SELECT
    stg.EMP_ID,
    stg.user_id,
    --stg.last_login_timestamp
    to_timestamp(stg.last_login_timestamp,'DDMMYYYYHH2 4MI')
    FROM a_stg stg

    Source table
    CREATE TABLE a_stg
    (emp_id varchar2(60),
    user_id varchar2(60),
    last_login_timestamp varchar2(60)
    )

    last_login_timestamp is as shown below in a_stg table

    07/12/2011 17:16:48
    08/13/2011 13:38:39
    08/15/2011 15:14:28
    08/13/2011 17:37:38
    08/04/2011 20:30:53
    08/12/2011 20:12:14
    08/15/2011 13:08:07
    06/30/2011 10:20:47
    08/10/2011 13:44:20
    08/13/2011 01:08:02

    Target table
    CREATE TABLE final_tab
    (emp_id varchar2(60),
    user_id varchar2(60),
    last_act varchar2(60)
    )

    The reason I have it(last_login_timestamp) defined as VARCHAR2 in the a_stg table is cause I'm reading the timestamp as a substring from another table what feeds it.
    v_timestamp := substr(field1,35,19)
    Last edited by nandinir; 08-24-11 at 13:29.

  13. #13
    Join Date
    Mar 2007
    Posts
    623
    Firstly let me repeat beilstwh's remark again: storing DATEs in VARCHAR2 column is really bad design, as it allows to enter non-existing DATEs (e.g. "99/99/9999 99:99:99" or even better "some crap I do not know") and makes the handling more difficult (as you can see with this "conversion" - it would not be needed when stored properly). The only justification could be when the DATE would be obtained from external text sources.

    Now you have two options:
    1) convert it to DATE and then back to VARCHAR2 with proper format masks:
    Code:
    TO_CHAR( TO_DATE( LAST_ACT_DSCR, 'mm/dd/yyyy hh:mm:ss' ), 'DDMMYYYYHH24MI' )
    Note it may fail when the LAST_ACT_DSCR contains invalid dates or total crap.

    2) do some string manipulation:
    Code:
    SUBSTR( LAST_ACT_DSCR, 4, 2 )||SUBSTR( LAST_ACT_DSCR, 1, 2 )||SUBSTR( LAST_ACT_DSCR, 7, 4 )
    ||<and so on for time components>
    Note it may return crap when the date is in different format (e.g. missing zeroes in day and/or month) or when the LAST_ACT_DSCR contains crap.

  14. #14
    Join Date
    Jul 2005
    Posts
    276
    I tried option 1 and it gives me this error -
    ORA-01810: format code appears twice

    SELECT TO_CHAR( TO_DATE( LAST_ACT, 'mm/dd/yyyy hh:mm:ss' ), 'DDMMYYYYHH24MI' ) FROM A_STG

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    while COPY & PASTE are working for you; proof reading seems to be lacking
    below works for me

    1* SELECT TO_CHAR( (TO_DATE( LAST_ACT, 'mm/dd/yyyy hh24:mi:ss' )), 'DDMMYYYYHH24MI' ) FROM A_STG
    SQL> /

    TO_CHAR((TO_
    ------------
    120820112012
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •