Alpha Video Training
Results 1 to 15 of 15

Thread: CTOD (What am I doing wrong??)

  1. #1
    "Certified" Alphaholic Rich Hartnett's Avatar
    Real Name
    Richard Hartnett
    Join Date
    Nov 2002
    Location
    Bowie, MD
    Posts
    1,464

    Default CTOD (What am I doing wrong??)

    I have a table with pre-existing dates in character format such as 07202006.

    I want to convert them to a standard date format such as 07/20/2006.

    I created a second field called "CorrectDate" and placed the following calculation in the field rules: ctod(date) (Where "date" is the original character string date.)

    But nothing happens upon peforming a recalculation of the table. Even removing one of the original character dates, clearing the field, and typing in a new character date does not return anything in the new calculated date field.

    Am I missing something? This is the correct function isn't it??

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: CTOD (What am I doing wrong??)

    Richard,

    Check the help file for a description of what ctod() does. You'll find it requires a formatted date string. I suspect the issue you're having is that "07202006" is not formatted correctly. -- tom

  3. #3
    Member
    Real Name
    Tony Rutherford
    Join Date
    Apr 2000
    Location
    Darwen, England
    Posts
    147

    Default Re: CTOD (What am I doing wrong??)

    Rich,

    Try this:

    date_value(val(right(yourdate,4)),val(left(yourdate,2)),val(substr(yourdate,3,2)))

    There may be a better way - I can't remember all the date functions.

    Tony

  4. #4
    "Certified" Alphaholic Rich Hartnett's Avatar
    Real Name
    Richard Hartnett
    Join Date
    Nov 2002
    Location
    Bowie, MD
    Posts
    1,464

    Default Re: CTOD (What am I doing wrong??)

    Hi Tom,

    I guess I'm not following. (Please excuse my ignorance) I got the ctod format from the help file, which reads as follows:

    "CTOD() returns a Date value extracted from a character string. For more information about date formats, see the DATE_FORMAT()function."

    What would need to be different about the character string of 07202006 for it to be "formatted correctly?" (I thought CTOD was formatting it correctly during the calculation.)

    Or better yet, is there a way to accomplish this with a different calculation?

    Thanks

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

    Default Re: CTOD (What am I doing wrong??)

    Parameter for ctod() reads

    A variable containing a date value or a character string containing a legitimate representation of a date value (ISDATE(Date_Representation) = .T.).
    Code:
    ? isdate("07202006")
    = .F.
    
    ? isdate("07/20/2006")
    = .T.
    
    
    ? isdate("07/20/06")
    = .T.
    
    
    ? isdate("7/20/2006")
    = .T.
    
    
    ? isdate("7/20/06")
    = .T.
    Not sure if this is a "better" solution but an alternative is

    Code:
    ? mask("07202006","  /  /    ")
    = "07/20/2006"
    
    ? ctod(mask("07202006","  /  /    "))
    = {07/20/2006}

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: CTOD (What am I doing wrong??)

    What would need to be different about the character string of 07202006 for it to be "formatted correctly?"
    As Stan has already observed,

    "07202006" would have to be converted to "07/20/2006". These are both character strings, but the latter is formatted correctly so that Alpha can recognize it as a representation of a date. I didn't mean to be obtuse, but the example in the help file discussion of CTOD() illustrates the use of the function by passing "12/12/05" to the function. I assumed you would understand immediately that the formatting of this string is distinctly different from the string you were trying to pass to the function. Sorry

    -- tom

  7. #7
    "Certified" Alphaholic Rich Hartnett's Avatar
    Real Name
    Richard Hartnett
    Join Date
    Nov 2002
    Location
    Bowie, MD
    Posts
    1,464

    Default Re: CTOD (What am I doing wrong??)

    Thanks Tony -

    Using your expression:

    date_value(val(right(yourdate,4)),val(left(yourdate,2)),val(substr(yourdate,3,2)))

    I get 01/04/0001, instead of 01/04/2007 for the character string of 01042007.

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

    Default Re: CTOD (What am I doing wrong??)

    Another solution is

    Code:
    c_date = "07202006"
    ?ctod(transform(c_date,"@R 99/99/9999"))
    = {07/20/2006}

  9. #9
    Member Bob Houle's Avatar
    Real Name
    Bob Houle
    Join Date
    Apr 2000
    Location
    Vancouver Island, Canada
    Posts
    332

    Default Re: CTOD (What am I doing wrong??)

    hmmm...

    Perhaps check your AlphaFive system DATE settings to see if they look correct.

    I'm using Win XP Pro SP2 and I get correct results, same as Tony.

    --Bob

  10. #10
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: CTOD (What am I doing wrong??)

    Rich:
    It's a bit subtle:
    dim a as c="07202006" 'NOT FORMATTED
    ?ctod(a)
    = { / / }

    a="07/20/2006" 'FORMATTED
    ?ctod(a)
    = {07/20/2006}

    You need:
    a="07202006"
    ?convert_type(a,"d")
    = {07/20/2006}
    Last edited by G Gabriel; 02-12-2007 at 12:36 PM.

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

    Default Re: CTOD (What am I doing wrong??)

    I think you tried convert_type() after you made the second assignment.

    Code:
    a = "07202006"
    ? convert_type(a,"D")
    = {  /  /    }

  12. #12
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: CTOD (What am I doing wrong??)

    dim a as c="07202006"
    b=mask(a," / / ")
    ?b
    = "07/20/2006"

    ?convert_type(b,"d")
    = {07/20/2006}

    In one line:
    ?convert_type(mask(a," / / "),"d")
    = {07/20/2006}

    when you have a s tring such as : 07202006 alpha has no clue which is the day, which is the month etc. Hence the need for formating the string by some means or another.

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

    Default Re: CTOD (What am I doing wrong??)

    And what is the perceived benefit to

    Code:
    convert_type(mask(a,"  /  /    "),"d")
    over

    Code:
    ctod(mask(a,"  /  /    "))
    Last edited by Stan Mathews; 02-12-2007 at 02:26 PM.

  14. #14
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: CTOD (What am I doing wrong??)

    not a thing...just got caught up in convert_type!

    Sorry Stan, didn't see your original reply!

  15. #15
    "Certified" Alphaholic Rich Hartnett's Avatar
    Real Name
    Richard Hartnett
    Join Date
    Nov 2002
    Location
    Bowie, MD
    Posts
    1,464

    Default Re: CTOD (What am I doing wrong??)

    Well that was certainly strange -

    I re-booted the machine, and now Tony's method works fine.

    date_value(val(right(yourdate,4)),val(left(yourdate,2)),val(substr(yourdate,3,2)))

    Once again, thanks everyone for all of your input and assistance.

Similar Threads

  1. What is Wrong Here?
    By Peter DeBilio in forum Alpha Five Version 5
    Replies: 1
    Last Post: 02-06-2003, 03:24 PM
  2. is this wrong ??
    By Edward F. Schulz in forum Alpha Five Version 5
    Replies: 8
    Last Post: 11-06-2002, 10:42 AM
  3. Xbasic - CTOD Problems with new computers
    By James Watson in forum Alpha Five Version 4
    Replies: 2
    Last Post: 03-21-2002, 04:05 AM
  4. What am I doing wrong
    By alexmuir in forum Announcements
    Replies: 6
    Last Post: 12-10-2000, 05:26 PM
  5. Problem with CTOD
    By Jeff Kerman in forum Alpha Four Version 6 and Prior
    Replies: 4
    Last Post: 08-17-2000, 07:31 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
  •