We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

## 5 replies

Last post Jul 26, 2011 07:04 AM by Mongol648

• Mongol648

Member

63 Points

451 Posts

### Converting Number to Time

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

• princeG

Contributor

6472 Points

1575 Posts

### Re: Converting Number to Time

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

• Mongol648

Member

63 Points

451 Posts

### Re: Converting Number to Time

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

• Mongol648

Member

63 Points

451 Posts

### Re: Converting Number to Time

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.

• hans_v

All-Star

25756 Points

7014 Posts

### Re: Converting Number to Time

#### Mongol648

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!

#### Mongol648

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

Int(859/100)

#### Mongol648

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

#### Mongol648

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

• Mongol648

Member

63 Points

451 Posts