Results 1 to 4 of 4

Thread: Date format

  1. #1
    Join Date
    Jan 2009
    Posts
    1

    Unanswered: Date format

    Hi,

    In my table, some of the records are having the date value as 'DD-MON-YY' format some are with 'YYYY-MM-DD HH24:MIS'.
    I want to update the later format with the earlier one. How do i select the records with
    The data type for this column is varchar2.
    I tried the following
    select to_char(
    to_date( datecol, 'YYYY-MM-DD HH24:MIS' ),
    'DD-MON-YY HH24:MIS' )
    from dual;
    Whats wrong with the above qry. its not working, any suggestions will be appreciated.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The data type for this column is varchar2.
    BAD!, BAD, Bad, bad
    Column should be replaced with a DATE datatype!

    >its not working
    My motor scooter is not working.
    Tell me how to make it go.


    do SELECT based upon location of leftmost dash character.
    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
    Mar 2007
    Posts
    623
    Quote Originally Posted by nerd30
    The data type for this column is varchar2.
    It is not a good idea as that column may contain any garbage and you have very limited way (or have to implement very complex algorithms) how to avoid this.
    Quote Originally Posted by nerd30
    I tried the following
    select to_char(
    to_date( datecol, 'YYYY-MM-DD HH24:MIS' ),
    'DD-MON-YY HH24:MIS' )
    from dual;
    Whats wrong with the above qry. its not working, any suggestions will be appreciated.
    If DATECOL has VARCHAR2 data type, there is nothing wrong with that query.
    There may be something wrong with data in DATECOL. If they are are in other format than expected or NULL, the conversion obviously fails. Take this permanent need of keeping correct data as a benefit of storing DATEs in VARCHAR2 column.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also as far as I know, the Dual table does not contain a column called datecol. and if it did, you would write it as

    Code:
    select TO_CHAR(to_date( datecol, 'YYYY-MM-DD HH24:MI:SS' ),'DD-MON-YY' )
     from dual
    WHERE INSTR(DATECOL,':') > 0;
    Last edited by beilstwh; 01-19-09 at 11:39.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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