locked
Converting Number to Time RRS feed

  • Question

  • User-883855585 posted

    I have a Access table with numbers stored like 859.  I need to convert that to a TIME which would be 8:59am or 0859.  What is the best way to accomplish this?

     

    Actually I only need the hour of the time.  So in this case (859) I would need the 8.  Could I use a combination of LEN and IIF?

     

    thanks

     

    Monday, July 25, 2011 12:45 PM

Answers

  • User-1199946673 posted

    I have a Access table with numbers stored like 859.  I need to convert that to a TIME which would be 8:59am or 0859.  What is the best way to accomplish this?

    The best way is to store dates or times as dattime!

    Actually I only need the hour of the time.  So in this case (859) I would need the 8

    Int(859/100)

    The data is variable in length, either 3 or 4 numbers (859, 1230, 1345, etc..)

    Int(859/100) = 8. Int(1230/100) = 12. Int(1345/100) = 13

    So Int([fieldname]/100) will return the result you want, but I don't think you're on the right track....

    Could I use a combination of LEN and IIF?

    Off course, you can use any combination of functions, but you're making things much more complucated as they really are....

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 25, 2011 5:14 PM

All replies

  • User-843484705 posted

    You need to use Substring function to get Hours

    string strHours= "859";

    strHours = strHours.SubString(0 ,strHours.Length-2);

    this is tricky. but only that sotution will be perfect for that 

    Monday, July 25, 2011 12:58 PM
  • User-883855585 posted

    I am having trouble seeing how this will work.  The data is variable in length, either 3 or 4 numbers (859, 1230, 1345, etc..)

    Monday, July 25, 2011 1:50 PM
  • User-883855585 posted

    I was thinking of doing LEN to get the length (3 or 4), then if it is 3 LEFT(fieldname,1) if it is 4 LEFT(fieldname,2).  Not sure if that could work in a query.

     

    Monday, July 25, 2011 2:20 PM
  • User-1199946673 posted

    I have a Access table with numbers stored like 859.  I need to convert that to a TIME which would be 8:59am or 0859.  What is the best way to accomplish this?

    The best way is to store dates or times as dattime!

    Actually I only need the hour of the time.  So in this case (859) I would need the 8

    Int(859/100)

    The data is variable in length, either 3 or 4 numbers (859, 1230, 1345, etc..)

    Int(859/100) = 8. Int(1230/100) = 12. Int(1345/100) = 13

    So Int([fieldname]/100) will return the result you want, but I don't think you're on the right track....

    Could I use a combination of LEN and IIF?

    Off course, you can use any combination of functions, but you're making things much more complucated as they really are....

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 25, 2011 5:14 PM
  • User-883855585 posted

    Thank you for the details.  I just was not seeing it for some reason.

     

    Thanks!!!

    Tuesday, July 26, 2011 7:04 AM