Access Data Conversion

[bytemedia]

Hey guys. I'm sort of new with VB.net but not too new. I know the basics.

I'm working with an Access database created for a Billing system. I have a column in the system called "HoursWorked". This column is a text datatype and all entries are in the x:xx format so an example entry would be "2:30" meaning they worked 2 and a half hours.

Here's what I need to do. In VB.net, I have to take the values in each of these fields and convert them to an integer. So I have to take something like "3:30" with a text data type and turn it into "3.5" integer data type.

Keep in mind that I cannot alter the database in any way. Which includes adding columns or changing column data types. And also keep in mind I have to do this for each field in a 309 record database. So setting it up as a function that I can call on is optimal.

 

[cjard]

Dim bits() as String 'array
bits = "2:30".Split(":")
Dim hrsWrkd as Decimal = Convert.ToDecimal(bits(0)) + (Convert.ToDecimal(bits(1))/60)

 

[cjard]

note tha tif you convert 3.5 into an integer, you get 3.

3.5 is not an integer

1,2,3,4,5,6,7,8,9 are integers
1.1, 2.76, 3.9745 are not integers

 

[JohnH]

Since it's a time span you can use the TimeSpan class:

Dim d As Double = TimeSpan.Parse("2:30").TotalHours

 

[cjard]

Good point; i often forget about TimeSpan.Parse because it is nearly 100% useless as it only accepts a limited set of inputs.. I wish it had ParseExact and ToString(string) methods but it doesnt, so I cant often use it..

In this case hh:mm is a parse style of the TimeSpan..

The s parameter contains a time interval specification of the form:

[ws][-]{ d | [d.]hh:mm[:ss[.ff]] }[ws]

Items in square brackets ([ and ]) are optional; one selection from the list of alternatives enclosed in braces ({ and }) and separated by vertical bars (|) is required; colons and periods and .) are literal characters and required; other items are as follows.