Alpha Video Training
Page 1 of 2 12 LastLast
Results 1 to 30 of 39

Thread: Convert character field to Time

  1. #1
    Member
    Real Name
    Dave Parkins
    Join Date
    Dec 2002
    Location
    Glen Haven, WI
    Posts
    289

    Default Convert character field to Time

    Hello,

    I have several tables that contain character fields that need to be converted to time fields.

    ex: 14:41:26 to 2:41:26pm

    When I tried changing the structure of the field to time, it displayed the time(14:41:26) as such: 00/00/0000 13:23:42 13 pm. Which is incorrect.

    There must be a script I can write to fix this I hope.

    Any help is appreciated.

    Thanks,
    Dave

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Baker
    Join Date
    Jun 2006
    Location
    Near Cherry Hill, NJ
    Posts
    1,296

    Default Re: Convert character field to Time

    Try

    Code:
    ctot("14:41:26")
    Tom Baker

  3. #3
    Member
    Real Name
    Dave Parkins
    Join Date
    Dec 2002
    Location
    Glen Haven, WI
    Posts
    289

    Default Re: Convert character field to Time

    Tom,

    Thanks, but that only works if the field is a time field already. The field is currently in character format.

    When I change the field structure to "short time", it creates a time that is not correct.

    ex: 14:41:26 should show up as 2:41pm. But, when I change the field structure to "short time", it changes it to 13:23:42 13 pm.

    Dave

  4. #4
    "Certified" Alphaholic
    Real Name
    Tom Baker
    Join Date
    Jun 2006
    Location
    Near Cherry Hill, NJ
    Posts
    1,296

    Default Re: Convert character field to Time

    Dave

    The ctot() function converts a character representation into a short time representation.

    Your example showed a character time representation.

    if you go to interactive and type

    ?ctot("14:41:26")

    it will return a time field equivalent of 2:41.26PM

    Maybe I misunderstood what you wanted.

    Tom Baker

    edit: Dave are you trying to convert the field in a table that is a character representation of a time value to a short time value by changing the table structure for that field from character to time?
    Last edited by Tbaker; 06-19-2008 at 11:31 AM. Reason: New question

  5. #5
    Member
    Real Name
    Dave Parkins
    Join Date
    Dec 2002
    Location
    Glen Haven, WI
    Posts
    289

    Default Re: Convert character field to Time

    "Dave are you trying to convert the field in a table that is a character representation of a time value to a short time value by changing the table structure for that field from character to time?"

    Yes Tom, exactly.

  6. #6
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,184

    Default Re: Convert character field to Time

    Dave,
    how about you add a new field to your table (tablename) as a Time field (t_field_name). Run the following script and then rename the time field after removing the character field (c_field_name).

    Code:
    dim tbl as P
    dim vcT as C 
    	
    	tbl = table.open("tablename")
    	query.filter = ""
    	query.order=""
    	query.options=""
    	tbl.query_create()
    	
    	tbl.fetch_first()
    	while .NOT. tbl.fetch_eof()
    		vcT = tbl.c_field_name
    	tbl.change_begin()
    		tbl.t_field_name = ctot(vcT)
    	tbl.change_end(.t.)
    	tbl.fetch_next()
    	end while
    	tbl.close()
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  7. #7
    Member
    Real Name
    Dave Parkins
    Join Date
    Dec 2002
    Location
    Glen Haven, WI
    Posts
    289

    Default Re: Convert character field to Time

    Mike,

    I ran the following code but all it did was delete all of the data in the character field.

    Did I key it in wrong?

    Thanks,
    Dave

    dim tbl as P
    dim vcT as C

    tbl = table.open("gate ab first and last 6_1_2008")
    query.filter = ""
    query.order=""
    query.options=""
    tbl.query_create()

    tbl.fetch_first()
    while .NOT. tbl.fetch_eof()
    vcT = tbl.diff
    tbl.change_begin()
    tbl.first_read = ctot(vcT)
    tbl.change_end(.t.)
    tbl.fetch_next()
    end while
    tbl.close()

  8. #8
    Member
    Real Name
    Dave Parkins
    Join Date
    Dec 2002
    Location
    Glen Haven, WI
    Posts
    289

    Default Re: Convert character field to Time

    Scratch that Mike. I had the fields mixed up. It appeared to work. Thanks. Now I'm trying to calculate the difference in time between the two new fields I created.

    Dave

  9. #9
    Member
    Real Name
    Dave Parkins
    Join Date
    Dec 2002
    Location
    Glen Haven, WI
    Posts
    289

    Default Re: Convert character field to Time

    Is there a simple function I can run to get the time difference. I had two character fields with timestamps in them. I want to now find the time difference between the two.

    ex. last_read - first_read = 2:30(two hours and 30 minutes)

  10. #10
    Member
    Real Name
    Doug Page
    Join Date
    Jan 2002
    Location
    Vancouver, BC Canada
    Posts
    963

    Default Re: Convert character field to Time

    Try looking here:

    http://support.alphasoftware.com/alp..._Functions.htm

    The HELP is your friend!

  11. #11
    Member
    Real Name
    Dave Parkins
    Join Date
    Dec 2002
    Location
    Glen Haven, WI
    Posts
    289

    Default Re: Convert character field to Time

    Thanks Doug. But I've been looking in Help for two plus days with no real specific luck. So, I thought I would post my question.

    Dave

  12. #12
    "Certified" Alphaholic
    Real Name
    Tom Baker
    Join Date
    Jun 2006
    Location
    Near Cherry Hill, NJ
    Posts
    1,296

    Default Re: Convert character field to Time

    Dave

    This is not an answer to your question but it would be better to post this as a new thread. I think that will get you some answers.

    Tom Baker

  13. #13
    Member
    Real Name
    Dave Parkins
    Join Date
    Dec 2002
    Location
    Glen Haven, WI
    Posts
    289

    Default Re: Convert character field to Time

    Thanks Tom.

  14. #14
    Member jaryder's Avatar
    Real Name
    Jeff Ryder
    Join Date
    May 2006
    Location
    Two Harbors, MN
    Posts
    349

    Default Re: Convert character field to Time

    Dave,

    Your question seemed to change a bit when you started a new thread but to answer your question you posted in this thread if your two time fields were defined short time then by subtracting the two fields you will get the result in seconds.

    Below is a sample of taking two time variables in character form, converting them to a short time and subtracting them and converting the result into hours and minutes. The totime function has several format options to return the result.

    Hope this helps.

    Jeff

    Code:
    dim start as c = "09:50 am"
    dim stop as c = "11:05 am"
    
    ?totime(ctot(stop) - ctot(start),1,0)
    = "1:15"

  15. #15
    Member
    Real Name
    Doug Page
    Join Date
    Jan 2002
    Location
    Vancouver, BC Canada
    Posts
    963

    Default Re: Convert character field to Time

    The first section of the help link is all on doing arithmetic with time and dates. Thought it would be good for you to review.

    Doug

  16. #16
    Member
    Real Name
    Dave Parkins
    Join Date
    Dec 2002
    Location
    Glen Haven, WI
    Posts
    289

    Default Re: Convert character field to Time

    Thanks Jeff,

    I did finally get the results I wanted. Now, I'm trying to trim down the results as I posted in my new thread.

    Thanks again,
    Dave

  17. #17
    Member
    Real Name
    Mark Williams
    Join Date
    Dec 2005
    Posts
    313

    Default Re: Convert character field to Time

    Quote Originally Posted by Mike Wilson View Post
    Dave,
    how about you add a new field to your table (tablename) as a Time field (t_field_name). Run the following script and then rename the time field after removing the character field (c_field_name).

    Code:
    dim tbl as P
    dim vcT as C 
    	
    	tbl = table.open("tablename")
    	query.filter = ""
    	query.order=""
    	query.options=""
    	tbl.query_create()
    	
    	tbl.fetch_first()
    	while .NOT. tbl.fetch_eof()
    		vcT = tbl.c_field_name
    	tbl.change_begin()
    		tbl.t_field_name = ctot(vcT)
    	tbl.change_end(.t.)
    	tbl.fetch_next()
    	end while
    	tbl.close()

    Sorry to revive an old thread but I've run into some difficulty running the above script.

    I have several hundred thousand records that need to be converted from a character field to a short time field.

    This script works fine except it aborts when it comes across a non-std entry in the character field. It aborts and then I have to go find the culprit record, correct it, re-run etc.

    Most are entered as 02:20PM, but it bombs if it finds something like 0220PM or anything other than the std format.

    Is there anyway to have it blank out any record that the character field is not in a correct format?

    Thanks
    Last edited by Mark Williams; 05-24-2010 at 07:22 AM.

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

    Default Re: Convert character field to Time

    Code:
    dim tbl as P
    dim vcT as C 
    	
    	tbl = table.open("tablename")
    	query.filter = ""
    	query.order=""
    	query.options=""
    	tbl.query_create()
    	
    	tbl.fetch_first()
    	while .NOT. tbl.fetch_eof()
    		vcT = tbl.c_field_name
    if istime(vcT)
    	tbl.change_begin()
    		tbl.t_field_name = ctot(vcT)
    	tbl.change_end(.t.)
    else
    tbl.change_begin()
    		tbl.t_field_name.blank_put()
    	tbl.change_end(.t.)
    end if
    
    	tbl.fetch_next()
    	end while
    	tbl.close()
    There can be only one.

  19. #19
    Member
    Real Name
    Mark Williams
    Join Date
    Dec 2005
    Posts
    313

    Default Re: Convert character field to Time

    Thanks Stan,

    One last dumb question...

    How can I get it to not update records where the source field (character field) are blank?

    Thanks

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

    Default Re: Convert character field to Time

    Should be able to use

    if istime(vcT) .and. alltrim(vcT)>""

    in place of the suggested

    if istime(vcT)
    There can be only one.

  21. #21
    Member
    Real Name
    Mark Williams
    Join Date
    Dec 2005
    Posts
    313

    Default Re: Convert character field to Time

    For some reason this doesn't work.

    I also tried
    if istime(vcT) .and. alltrim(vcT)<>""

    I could deal with it converting all the blank character time fields to 12:00:00:00 am if there was a way to go back into the new fields and blank all records where the value = 12:00:00:00 am

    There isn't a pre-defined operation to blank a time field.

    Is there any other way to do it?

    Thanks

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

    Default Re: Convert character field to Time

    I'm curious why the istime() function is returning true for blank character fields. Does it seem to anyone but me that this might be an error (bug) in the IsTime() function?

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

    Default Re: Convert character field to Time

    How about:

    if (.not. isblank("your_c_fieldname")) .and. istime(vcT)

  24. #24
    Member
    Real Name
    Mark Williams
    Join Date
    Dec 2005
    Posts
    313

    Default Re: Convert character field to Time

    Quote Originally Posted by Tom Cone Jr View Post
    How about:

    if (.not. isblank("your_c_fieldname")) .and. istime(vcT)
    Tom,
    I am getting the same results using the above.

    If it matters, I am using v9 even though this is the v8 forum where I dug this thread up from.

    I can make the whole thing work if I can find a way to blank out all fields in the short time field that = 12:00:00:00 am afterwards.

    Thanks

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

    Default Re: Convert character field to Time

    Mark, did you include quote marks around the field name you passed to the isblank() function? I'm shooting in the dark here cause there's no data to work with.

    I should think you could define an update operation that was filtered on the 12:00:00:00 records, and then use the null_value() function to "blank" the field values.

  26. #26
    Member
    Real Name
    Mark Williams
    Join Date
    Dec 2005
    Posts
    313

    Default Re: Convert character field to Time

    Ok, I just created a test database for this and am getting very odd results. I created a character time field with data similar as that in our real app and a short time field to update from the character field.

    Here are some of the results I am getting:

    09:10a converts to 09:10:00:00 am (OK)
    12:35p converts to 12:00:00:00 am (Wrong - a5 assumes this isn't valid time?)
    08:23A converts to 08:23:00:00 am (OK)
    0823A converts to 12:00:00:00 am (Not valid time format as there is no hyphen)
    01:25P converts to 01:25:00:00 pm (OK)
    (Blank) converts to 12:00:00:00 am

    etc...

    Any help appreciated...

  27. #27
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,578

    Default Re: Convert character field to Time

    Mark

    Try this...
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  28. #28
    Member
    Real Name
    Mark Williams
    Join Date
    Dec 2005
    Posts
    313

    Default Re: Convert character field to Time

    Quote Originally Posted by Al Buchholz View Post
    Mark

    Try this...
    Al,
    This appears to be working fine except it is still entering 12:00:00:00 am in the short time field when the source field is blank.

    Thanks,
    Mark

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

    Default Re: Convert character field to Time

    Just noticed something.

    is

    12:00:00:00 am

    a legitimate time?

    shouldn't it be pm?

    or

    00:00:00:00 am


    Consider this:

    dim myStart as Y
    mystring = " "
    myStart = ctot(mystring)
    ?mystart
    = 12:00:00 00 am

    ?istime(mystring)
    = .T.

    ?cShorttime(myStart)
    = "000000000"

    Later -

    Turns out 12:00:00 00 am is the null value for short time fields. check the help file topic "Null Table Field Values"

    If this has been obvious to everyone but me I'll shut up if you promise not to laugh.

    -- tom
    Last edited by Tom Cone Jr; 05-25-2010 at 03:32 PM.

  30. #30
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,578

    Default Re: Convert character field to Time

    Quote Originally Posted by Mark Williams View Post
    Al,
    This appears to be working fine except it is still entering 12:00:00:00 am in the short time field when the source field is blank.

    Thanks,
    Mark
    So take the comments off the if then and take out the istime() portion of the if.....
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

Similar Threads

  1. Convert long time field
    By George Corder in forum Alpha Five Version 8
    Replies: 2
    Last Post: 01-06-2008, 03:10 PM
  2. formatting absolute time values from a character field
    By trackmanpete in forum Alpha Five Version 8
    Replies: 7
    Last Post: 03-16-2007, 09:07 AM
  3. changing character field to time field
    By foodminer in forum Alpha Five Version 8
    Replies: 16
    Last Post: 03-08-2007, 02:16 AM
  4. Convert date to time field
    By rlgiordano in forum Alpha Five Version 7
    Replies: 2
    Last Post: 10-01-2006, 12:13 PM
  5. Character field format for Time
    By forskare in forum Alpha Five Version 4
    Replies: 3
    Last Post: 10-09-2000, 10:10 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
  •