Alpha DevCon 2018
Results 1 to 15 of 15

Thread: Converting character to a date field?

  1. #1
    Howard Berg
    Guest

    Default Converting character to a date field?

    Code:
    Is it possible to convert a character field which has
    dates listed,e.g., like 4.99, 3.98, 11.95 to a date field
    like the default shows?  Keeping the month and year as it
    now stands?
         I wish to do this so I can append them to a current
    table with their dates secured in the right format. I don't
    mind inserting any day for middle choice to get right format
    of _/__/__.
         Thanks
    Howard Berg

  2. #2
    "Certified" Alphaholic
    Real Name
    William Hanigsberg
    Join Date
    Apr 2000
    Location
    Toronto, ON
    Posts
    4,018

    Default RE: Converting character to a date field?

    Howard:

    Check out the CTOD function. CTOD stands for character to date. For example, ctod("12/12/95") would yield a date. So the character expression "4.99" could be reformatted and then transformed with ctod.

    This is the logic:
    input="4.99"
    datestr=left(input,1)+chr(47)+"1"+chr(47)+right(input,2)
    resultdate=ctod(datestr)

    Chr(47) is the backslash and I wrote it this way because the board eats backslashes. You could just as well use the backslash itself.

    I would write a custom function it I had much of this to do.

    Hope this gets you going.

    Bill

  3. #3
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,755

    Default RE: Converting character to a date field?

    Here's an improvement on Bill's idea to take care of months with either 1 or 2 digits:

    input="4.99"

    datestr=subst(input,1, at(".",input)-1 ) +"\01\"+ right(input,2)
    resultdate = ctod(datestr)

    (How'd I do them thar backslashes? e-mail me to find out!)

  4. #4
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,755

    Default RE: Converting character to a date field?

    What was I thinking? They should be forward slashes anyway! (I also added color to one part of the expression just for clarification.)

    datestr = subst(input,1, at(".",input)-1 ) + "/01/" + right(input,2)
    resultdate = ctod(datestr)

  5. #5
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,755

    Default RE: Converting character to a date field?

    I can't get anything right tonight! Left out the "r" in "substr". (If anything else is wrong you'll have to find it yourself!)

    datestr = substr(input,1, at(".",input)-1 ) + "/01/" + right(input,2)
    resultdate = ctod(datestr)

  6. #6
    "Certified" Alphaholic
    Real Name
    William Hanigsberg
    Join Date
    Apr 2000
    Location
    Toronto, ON
    Posts
    4,018

    Default RE: Converting character to a date field?

    Hi Cal,

    You're not the only one who was tired last night. All the while I was talking about backslashes I was writing forward slashes!

    All the best,
    Bill

  7. #7
    Howard Berg
    Guest

    Default RE: Converting character to a date field?

    Code:
    William & Cal,
     Thanks VERY much. This will get me going.
    Nice to be pointed in the right direction.
    I knew of "CDOT"; but did know what exactly
    to do with it. Especially since converting
    from characters like "4.99" etc. which are not
    in proper form yet.
       I'll go to work on it and learn thereby.
    Thx
    Howard

  8. #8
    Howard Berg
    Guest

    Default RE: Converting character to a date field?

    Code:
    I've enjoyed learning from all the help how "substr","cdot" and "at" will go and change the character string "." which
    populates my one field and change it to date data. Very cool!
    
    datestr = substr(input,1, at(".",input)-1 ) + "/01/" + right(input,2)
    resultdate = ctod(datestr) 
    
    Now how does one launch it or start it on its way to search and change the whole field? By writing it all the way as a script? And will that change the restructuring for the field for future entries or do I do that later?
    
    I assume "input" is the name of my field:  "Last_in" ?
    
    Thx.
    Pupil # 34
    
    Howard Berg

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

    Default RE: Converting character to a date field?

    A cautious way would be to create a new empty table to hold your unique value from the table where the "bad" date data resides and a field to contain the new date value. Then do a post operation posting the unique value as is and your "expression"

    resultdate = ctod(substr(input,1, at(".",input)-1 ) + "/01/" + right(input,2))

    to the date value field. Verify that you are satisfied with the results, edit the structure of the oiginal table to accept the date field value by redefining the original field or adding a new field, then post the dates back to the original table.

  10. #10
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,755

    Default RE: Converting character to a date field?

    The ultimate caution - MAKE A BACKUP!

    Another way to update it...
    - Add a new date field to your current table.
    - Use the expression above (substr(input.... ) as the definition for an Update operation where "input" is the name of your current field.
    - Hint: make sure you don't have an active query or filtered index when you run the update.
    - When you're SURE the new dates are correct, delete the old field. (And, keep the backup for a year or so just in case!)

    This avoids the need to worry about posting (and what to use for the unique field) and it still protects your data because you don't change anything but the new field which was originally blank anyway. Besides, if you really goof, you've got a backup!

  11. #11
    Howard Berg
    Guest

    Default RE: Converting character to a date field?

    Code:
    Yes, the BACKUP I have done because of the importance of these records; already saved my bacon when I initially just changed the field in question to a date field and lost all the material in the field, despite the warning message given. So I've learned the value of backing up.
    
    So then I came to the forum. Only pose the questions after reading the manuals and trying a few other ways. That way I
    retain what I learn about this awesome program.
    
    Appreciate your suggestions. 
    
    Howard

  12. #12
    Howard Berg
    Guest

    Default RE: Converting character to a date field?

    Code:
    I've added a new date field to my table and set-up an update operation using the expression
    
    ctod(substr(input,1, at(".",input)-1 ) + "/01/" + right(input,2)) 
    
    
    but all the year dates come out 2000, instead of keeping the correct year from the "." string which show as "4.95"
    or "12.97", eg. 
    Should not the expression be returning them? Which is the goal of the execise?
    Howard Berg

  13. #13
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,850

    Default RE: Converting character to a date field?

    Howard,

    Just did this in the code editor, interactive tab:

    input = "3.97"
    ? ctod(substr(input,1, at(".",input)-1 ) + "/01/" + right(input,2))
    = {03/01/1997}

    Got the expected result. Then put in

    input = "3.97 " (trailing spaces) and got

    ? ctod(substr(input,1, at(".",input)-1 ) + "/01/" + right(input,2))
    = {03/01/2000}

    You need to trim the spaces in your text field "input". I prefer ALLTRIM(input)

    ctod(substr(ALLTRIM(input),1, at(".",ALLTRIM(input))-1 ) + "/01/" + right(ALLTRIM(input),2)) .

    Good luck.

  14. #14
    Selig Starr
    Guest

    Default RE: Converting character to a date field?

    Howard,
    A small thing. Can you post your messages in single space?

  15. #15
    Howard Berg
    Guest

    Default RE: Converting character to a date field?











    Stan,Using Alltrim worked for sure, just as you suggested.
    What a nice lesson in learning about "cdot","substr", "at", and
    "Alltrim". For many of us new to A5 examples like this and others on
    the board are of serious importance. Now that I finally got my
    printed copy of XBasic Ref. Manual I'll be able to read further.
    Thanks again to you, and Cal and Bill for the help.
    Howard

Similar Threads

  1. Transform character field to date field, how ?
    By mronck in forum Alpha Five Version 5
    Replies: 9
    Last Post: 11-11-2003, 11:38 AM
  2. Changing date to character
    By BRIAN GRAY in forum Alpha Five Version 5
    Replies: 1
    Last Post: 09-27-2003, 03:34 PM
  3. Converting Character to Numeric
    By Red Abicht in forum Alpha Five Version 4
    Replies: 1
    Last Post: 10-03-2002, 09:12 AM
  4. Formatting a character date field
    By Frank Lucenti in forum Alpha Five Version 4
    Replies: 4
    Last Post: 05-23-2002, 01:45 PM
  5. converting numeric to character data
    By Oran Charlson in forum Alpha Four Version 6 and Prior
    Replies: 4
    Last Post: 07-23-2001, 03:19 PM

Posting Permissions

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