# Converting Number to Time

• ### 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

• 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 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 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