How to display Duration only Hours: Minutes: Second in Asp.Net Gridview using LINQ to SQL?
I want to display duration only Hour, Minute and Second in Gridview data by Subtract TimeCheckOut from TimeCheckIn in ASP.NET using LINQ to SQL
Here is the code behind:
Dim db = new MyDataContext
Dim user = from u in db.Employees select IDNumber = u.IDNumber, _
FirstName = u.firstName, LastName = u.lastName, TimeCheckIn = u.timeCheckIn, _
TimeCheckOut = u.timeCheckOut, Duration = u.timeCheckIn
Gridview1.DataSource = user
Gridview1.DataBind()
Code on page:
<asp:GridView ID="GridView1" runat="server" Width="100%"
AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="IDNumber" HeaderText="ID Number" ReadOnly="True" />
<asp:BoundField DataField="FirstName" HeaderText="First Name" ReadOnly="True"/>
<asp:BoundField DataField="LastName" HeaderText="Last Name" ReadOnly="True"/>
<asp:BoundField DataField="TimeCheckIn" HeaderText="Time Check In" ReadOnly="True"/>
<asp:BoundField DataField="TimeCheckOut" HeaderText="Time Check Out" ReadOnly="True" />
<asp:TemplateField HeaderText="Duration">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# FieldDisplayDuration(Eval("Duration")) %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
Here's my helper for FieldDisplayDuration:
Protected Function FieldDisplayDuration(ByVal Duration As DateTime) As String
Dim rtn As String = "DefaultValue"
Dim dif As TimeSpan = DateTime.Now.Subtract(Duration)
rtn = dif.Hours & " hours, " & dif.Minutes & " minutes, " & dif.Seconds & " seconds. "
Return rtn
End Function
On line 3, in the helper function Dim diff as TimeSpan = DateTime.Now.Subtract (Duration) which only gives the duration of Hour, Minute and Second from TimeCheckIn to DateTime.Now. However, I want to have a duration from TimeCheckIn to TimeCheckOut in only hours, minutes and seconds. I know the FieldDisplayDuration function is completely wrong logic, but I just want you to get my point, and it could also be a sample code for anyone looking to calculate the duration of an employee from the date of hiring. Finally, let's get back to TimeSpan by subtracting TimeCheckOut from TimeCheckIn in the gridview issue. How can i do this? Please give me the key. Thank you very much...
a source to share
You can just subtract the date and time to get the Timespan so you can use
Text='<%# FieldDisplayDuration(Eval("TimeCheckIn"), Eval("TimeCheckOut")) %>'
and something like this function:
Protected Function FieldDisplayDuration(ByVal CheckIn As DateTime, ByVal CheckOut as DateTime) As String
Dim rtn As String = "DefaultValue"
Dim dif As TimeSpan = CheckOut - CheckIn
rtn = dif.Hours & " hours, " & dif.Minutes & " minutes, " & dif.Seconds & " seconds. "
Return rtnEnd
Function
Although I would probably use the string format on the rturn string and do it ...
rtn = string.Format("{0} hours, {1} minutes, {2} seconds.", dif.Hours, dif.Minutes, difSeconds)
It seems to me easier to read.
a source to share
You can put logic to calculate the duration right in your Select clause, for example:
Dim user = from u in db.Employees select IDNumber = u.IDNumber, _
FirstName = u.firstName, LastName = u.lastName, TimeCheckIn = u.timeCheckIn, _
TimeCheckOut = u.timeCheckOut, Duration = u.timeCheckOut.Subtract(u.timeCheckIn)
Then you only need to format the resulting timeslot.
Alternatively, you can do the whole thing in a selection, or even call a function, so there is something like
Duration = FormatDuration(u.timeCheckOut.Subtract(u.timeCheckIn)
or
Duration = FormatDuration(u.timeCheckIn, u.timeCheckOut)\
If you did this and assuming FormatDuration returned a string, you could completely remove the column template
a source to share