Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    AU
    Posts
    19

    Arrow Unanswered: Date conversion?

    hey all,

    i'd say this is probably pretty basic.... but here goes.

    i just want to convert some dates from 'YY-MM-DD' to 'YYYYDDD'

    but using the following line i get an error saying that it's an invalid number format model

    to_char(to_number(date, 'YY-MM-DD'), 'YYYYDDD')

    any ideas?

    calico

  2. #2
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: Date conversion?

    which date-type is your 'date' ??
    if it's DATE just use to_char(date, 'YYYYDDD')
    if it's some kind of string use
    to_char( to_date(date, 'YY-MM-DD'), 'YYYYDDD')

    to_char needs a value of type DATE as 1st parameter.
    ^/\x

  3. #3
    Join Date
    Feb 2003
    Location
    AU
    Posts
    19
    no the initial variable is a char (eg. '03-07-30') and i assume that it should be converted to a date and then converted back to a char (format 'YYYYDDD').

    but the following command returns an error: ORA-01481: invalid number format model

    Code:
    select to_char(to_number('03-07-30', 'YY-MM-DD'), 'YYYYDDD') from dual;

    calico

  4. #4
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102
    if you want to convert the string '03-07-30' to the string '2003211' (both as string-representation of a date)
    don't convert to_number but to_date:


    SQL> select to_char(to_date('03-07-30', 'YY-MM-DD'), 'YYYYDDD') from dual;

    TO_CHAR
    -------
    2003211
    ^/\x

  5. #5
    Join Date
    Feb 2003
    Location
    AU
    Posts
    19

    Red face

    dohhhh! thanks for your help, what a silly mistake!

    calico

Posting Permissions

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