SQL & ASP.net Hours?

Bakedgoods

Bakedgoods

Bakedgoods

Capodecina
Permabanned
Joined
31 Dec 2003
Posts
5,172
Location
Barrow-In-Furness
MS SQL

Just to confirm, I need to store the number of hours a user has spent doing something, am I correct using the smalldatetime field for the MS SQL database?

I will eventually need to be able to calculate totals using SQL Reporting Services so it's important that the hours add up correctly (to 60 minutes, not 100).

ASP.NET

I'm trying to find the correct sort of formatting to use? Short time stores the time as 9:50AM, which isn't what i'm after.

I need to store the hours as in 1 hour, or 01:00:00 (1 hour, 0 minutes, 0 seconds) or just 01:00 for 1 hour. I do need minutes but seconds are not required.

I can't find the right formatting for this, would Long time be more suited?
 
I'd store it as an integer in the database of minutes or seconds, if you need to go to by-the-second detail.

In .NET I'd use the TimeSpan class.
 
huppy said:
I'd store it as an integer in the database of minutes or seconds, if you need to go to by-the-second detail.

In .NET I'd use the TimeSpan class.
Click to expand...

Doesn't that mean i'll have to use code to convert it from minutes to hours?

I thought there's be an easy way of storing hours so SQL would be able to do a sum on it :(

I could really do with storing it as hours rather than minutesm it'd make my life a lot easier and less editing elsewhere.
 
If you don't need minute accuracy, store the hours as an integer in the db instead of minutes.

As soon as it's loaded as a TimeSpan in .NET, converting between hours/minutes/seconds/days/years is as easy as pie
 
Right... i'm going to store it as minutes (Integer) and they try to come up with a function that will convert it back to hours when it's being displayed.

Expect another post soon.... :D
 
I would convert it at data layer, at the SQL database like so:

SELECT CONVERT(varchar, FLOOR(ElapsedTime / 60.0)) + ':' + RIGHT('0' + CONVERT(varchar, ElapsedTime % 60), 2) AS HrMin
FROM SomeTable

Stelly
 
you could do it in VB like so:

x = 250
y = x \ 60 'hours
z = x Mod 60 'minutes
Print "hours=" & y, "minutes=" & z

that converts 250 Minutes to this 4 hours and 10 minutes...

Stelly
 
Code: 
Dim time As Integer

time = 72

Dim mins As Integer = time Mod 60

Dim hours As Integer = (time - mins) / 60

Dim total As String = hours & ":" & mins

Does that seem ok?

I was going to convert it when i'd already pulled the data, otherwise I have to change loads of queries :x

I'm already sifting through the huge stored proc you did for me and changing field types
 
I’ve got the function working, but I keep getting a crappy little error and I can’t spot why…

Code: 
Private Function ConvertMinutesToHours(ByVal Time As Integer) 

        Dim mins As Integer = Time Mod 60
        Dim hours As Integer = (Time - mins) / 60
        Dim total As String = hours & ":" & mins 

        Return total 

    End Function    

    Protected Sub UnapprovedHoursGridView_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles UnapprovedHoursGridView.DataBound

        Dim hoursAdapter As New ManHoursTableAdapters.tblHoursTableAdapter

        Dim hours As ManHours.tblHoursDataTable = hoursAdapter.GetUnapprovedLoggedHours(Me.User.Identity.Name.ToString())

        Dim Time As Integer

        Dim Total As String 

        For Each dr As System.Data.DataRow In hours.Rows()

            Time = dr("Hours")

            Total = ConvertMinutesToHours(Time) 

            Dim HoursLabel As Label

            HoursLabel = CType(UnapprovedHoursGridView.FindControl("HoursLabel"), Label) 

            HoursLabel.Text = Total  [B][COLOR="Red"][U]Object reference not set to an instance of an object.[/U][/COLOR][/B]
        Next

    End Sub

Any ideas?

The HoursLabel is the name of a Label in my GridView…. I can’t workout why it’s giving me that error everything looks spot on
 
Its not finding your label... make sure that you have the name right.... also try this...

HoursLabel.Text = "Test" to see if its finding the label

Stelly
 
The code was slightly wrong anyways, got it all sorted now, but can't workout how to set the text of the GridView, the name etc is spot on and someone else has even taken a look....

Code: 
Protected Sub UnapprovedHoursGridView_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles UnapprovedHoursGridView.RowDataBound
        If e.Row.DataItemIndex <> -1 Then
            Dim Time As Integer = e.Row.DataItem("Hours")
            Dim Total As String

            Total = ConvertMinutesToHours(Time)

Can anyone tell me how to set a label in the GridView to Total?
 
Code: 
 If e.Row.DataItemIndex <> -1 Then
            Dim Time As Integer
            Dim Total As String
            Dim i As Label = e.Row.FindControl("HoursLabel")

            Time = i.Text
            Total = ConvertMinutesToHours(Time)

            i.Text = Total
 
You must log in or register to reply here.
Back
Top Bottom