Alpha Video Training
Results 1 to 10 of 10

Thread: Transform character field to date field, how ?

  1. #1
    Banned
    Join Date
    Aug 2003
    Posts
    1,416

    Default Transform character field to date field, how ?

    I have a character field containing a date (from an ASCII import). I want to transform that field to a normal date field, but can't get it working. I tried the CTOD() command but probably do not use it the way it should be.

    Can anyone advise me ?

    The case is like this:
    CHARACTER FIELD = 20031205 (being 05 december 2003)
    How do I change this to a valid A5 date field ?

    Regards,

    Marcel

  2. #2
    Thomas Henkel
    Guest

    Default RE: Transform character field to date field, how ?

    Its kind of funny that it needs to be converted, because that is the format that it is stored as a date.

    Try this:

    CTOD(substr(your_field,5,2)+"/"+right(your_field,2)+"/"+left(your_field,4))


    What this will do is turn your field "20031205" into a VALID character string date field "12/05/2003", then convert it into a valid date format date field "20031205".

    Seems silly, but this should do the trick.

    Tom

  3. #3
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Transform character field to date field, how ?

    CHARACTER_FIELD = "20031205"

    left(CHARACTER_FIELD,4) = "2003"
    substr(CHARACTER_FIELD,5,2) = "12"
    right(CHARACTER_FIELD2) = "05"

    char_date_field = substr(CHARACTER_FIELD,5,2)+chr(92)+right(CHARACTER_FIELD2)+chr(92)+left(CHARACTER_FIELD,4) = "2003"

    then take

    dtoc(char_date_field)

  4. #4
    Former Alpha Employee JerryBrightbill's Avatar
    Real Name
    Jerry Brightbill
    Join Date
    Apr 2000
    Posts
    5,172

    Default RE: Transform character field to date field, how ?

    There is actually and easier way than shown above. From the interactive window

    date1 = "20031205"
    ?odbc_dt_to_d(transform(date1,"@r 9999-99-99"))
    = {12/05/2003}

    This outputs a date in date format.

    Jerry

  5. #5
    Banned
    Join Date
    Aug 2003
    Posts
    1,416

    Default RE: Transform character field to date field, how ?

    Thomas, Stan, Jerry: Thanks !! It also gives something to study I must say. Much obliged.

    Regards,

    Marcel

  6. #6
    Banned
    Join Date
    Aug 2003
    Posts
    1,416

    Default RE: Transform character field to date field, how ?

    Hi Guys,

    Excuse me Alpha teachers, but it seems I need another of your Colleges.

    How do I get either one of your advices (which I studied) enter in the Expression Builder to link to a field rule ?
    It keeps telling me the sequence is OK but it does not compute to a date format.

    Jerry, I get your advice in the interactive window to work OK, but how do I transform it into a field rule ?
    I can't get Stans' and Thomas' solution working in the expression builder as well.

    Of course this has nothing to do with the solutions but more with my limited knowledge, I know.
    Can you help me with it ?

    Regards,

    Marcel

  7. #7
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Transform character field to date field, how ?

    As usual, Jerry's solution is probably best. To implement it you have choices. One is to add a field to the database into which the original date is imported, make the new field calculated, with the expression

    odbc_dt_to_d(transform(date1,"@r 9999-99-99"))

    and change "date1" to the name of the actual field in your table where the "20031205" type data is appearing.

    Another solution would be to have another table entirely, post the data from the table to which you do the import, and when the "20031205" is posted, post odbc_dt_to_d(transform(date1,"@r 9999-99-99")) to a date field

  8. #8
    Banned
    Join Date
    Aug 2003
    Posts
    1,416

    Default RE: Transform character field to date field, how ?

    Hi Stan !

    Over here in Holland it is a very nice evening !

    Sorry to keep you bussy with this little problem, but I have another question connected to your answer.
    Could you have your command
    odbc_dt_to_d(transform(date1,"@r 9999-99-99"))
    implemented in an operation that you could run silently directly after importing ?
    What do you think about that ? Possible / no ?

    Regards,

    Marcel

  9. #9
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Transform character field to date field, how ?

    Since the date information is being imported, incorrectly, as a text field, you cannot use a defined operation to change it's type (transform from text to date) because the field where it is stored will not accept a date, and you cannot increase the available field width within an operation.

    If the field width of the imported table field is 8 characters, there is nothing you can do with an operation.

    IF the field width is ten, due to some other misinterpretation of the data, and if you could accept the character limitation and want to create an update operation to change

    "20031205" into a VALID CHARACTER string "12/05/2003"

    then Tom's "inside portion" of his suggestion

    substr(your_field,5,2)+"/"+right(your_field,2)+"/"+left(your_field,4)

    would be the expression to use in that operation.

    At this point, when you need the date, just refer to it with ctod(your_field) since you have re-arranged the characters.

    But all this seem like a lot of effort for little gain. Whenever you need the date, you could just refer to it with Jerry's odbc_dt_to_d(transform(date1,"@r 9999-99-99")). This expression could be used in date calculations or any where you want to display the date.

    Why does it matter how the data is stored, if you know how to use it?

  10. #10
    Banned
    Join Date
    Aug 2003
    Posts
    1,416

    Default RE: Transform character field to date field, how ?

    Yes, you are right there. It doesn't.
    Okay, thanks !

Similar Threads

  1. import a numeric field into a character field
    By dcorcoran in forum Alpha Five Version 6
    Replies: 5
    Last Post: 04-02-2005, 09:44 AM
  2. Changing date field when set as calculated field
    By Cheryl Lemire in forum Alpha Five Version 5
    Replies: 2
    Last Post: 08-08-2004, 10:48 AM
  3. How to transform modern julian date
    By George Corder in forum Alpha Five Version 5
    Replies: 2
    Last Post: 12-20-2003, 04:59 AM
  4. Formatting a character date field
    By Frank Lucenti in forum Alpha Five Version 4
    Replies: 4
    Last Post: 05-23-2002, 02:45 PM
  5. Converting character to a date field?
    By Howard Berg in forum Alpha Five Version 4
    Replies: 14
    Last Post: 05-11-2000, 07:30 AM

Bookmarks

Posting Permissions

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