Displaying time in another format from database formating

[MUHAMMED DÖNMEZ]

Hello agian syncfusion team,

I have query from database to calculate work hours in (time format HH:mm:ss), I get the result from query correctly , the query  :

SELECT ScdID, EmpID, CONCAT(EmpName, ' ',EmpLastName) AS EmpFullName, EmpCode,
    MIN(DATE(ScdEmpIN)) AS xDATEStart,
    MAX(DATE(ScdEmpOut)) AS xDATELast,
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(ScdEmpOut, ScdEmpIN)))) AS TOTAL
    FROM tblschedule
    INNER JOIN tblemp ON tblschedule.ScdEmpID = tblemp.EmpID
    GROUP BY ScdEmpID ORDER BY ScdEmpIN


the results what I want appears like this :

The results I want in red square shape but in datagrid is showing like this:

means that if time greater than 24 hours showing like first and second and 5th row(1 day and 5 hours and 45 min .....) and the other normally displayed..

My datagrid :

How can I resolve this problem to display time as total as database qury format

I tried to format the datagrid cell like adding GridDateColumn and have no result as I want 

also I tried to format the GridTextColumn (HH:mm:ss) also no result as I want ..

I am using visual studio 2022 c# 

Is there way to resolve it 

hope to hear from you 

Regards ...

[Manikanda Akash Munisamy]

Hi MUHAMMED DÖNMEZ,

We have reviewed your scenario where the time difference between two DateTime values isn't displaying as expected when it exceeds 24 hours. In .NET, subtracting two DateTime values produces a TimeSpan. If the difference exceeds 24 hours, it's displayed in the format days:hours:minutes:seconds, so a difference of 29 hours and 45 minutes appears as 1.05:45:00 (1 day, 5 hours, and 45 minutes) instead of 29:45:00.

Since there isn't a direct format string in TimeSpan to display total hours exceeding 24 hours, we've prepared a workaround at the sample level. Assuming your SQL query is set to SfDataGrid.ItemsSource as a DataTable, we created a DataTable, performed the time difference operation, and added the result in another column with a custom format.

Code Snippet

foreach (System.Data.DataRow row in dataTable.Rows)
{
     DateTime startDate = Convert.ToDateTime(row["StartDate"]);
     DateTime endDate = Convert.ToDateTime(row["EndDate"]);
     TimeSpan timeSpan = endDate - startDate;

     string formattedTimeDifference = $"{(int)timeSpan.TotalHours:D2}:{timeSpan.Minutes:D2}:{timeSpan.Seconds:D2}";
     row["TimeDifference"] = formattedTimeDifference;
}

Please find the attached sample that demonstrates this solution. If we've misunderstood your requirement, please provide more details so we can better understand the issue and offer an appropriate solution.

We look forward to your response

Regards,
Manikanda Akash 

 

Attachment: SfDataGrid_Demo_4_8_4f59a3e2.zip

[MUHAMMED DÖNMEZ]

Hi  Manikanda Akash,

Thank you for your responding and code ,

Since .NET displayed in the format days:hours:minutes:seconds becuase I already retrieved the data from database as Time, I made a littile trick,

I  retrieved a new field from data base as (string or varchar) then I stored the date in new column in datagridview as I want ((hours:minutes:seconds)).. 

This is myfunction :

public string ConvertToHoursMinutesSeconds(string xInput)
{
    // Split the input string by colon
    if (xInput.Contains("."))
    {
        string[] xTimeParts1 = xInput.Split(':');
        string[] xTimeParts2 = xInput.Split('.');


        // Parse days, hours, minutes, and seconds
        int xDays = int.Parse(xTimeParts2[0]);
        string xxTimeParts = xTimeParts2[1];
        string[] xTimeParts3 = xxTimeParts.Split(':');
        int xHours = int.Parse(xTimeParts3[0]);
        int xMinutes = int.Parse(xTimeParts3[1]);
        int xSeconds = int.Parse(xTimeParts3[2]);
        // Convert days to hours
        int xTotalHours = xDays * 24 + xHours;
        // Format the result as hours:minutes:seconds
        if (xMinutes < 10)
        {
            return $"{xTotalHours}:{"0" + xMinutes}:{"0" + xSeconds}";
        }
        else
        {
            return $"{xTotalHours}:{xMinutes}:{"0" + xSeconds}";
        }
    }
    else
    {
        string[] xTimeParts = xInput.Split(':');
        int xHours = int.Parse(xTimeParts[0]);
        int xMinutes = int.Parse(xTimeParts[1]);
        int xSeconds = int.Parse(xTimeParts[2]);
        // Convert days to hours
        int xTotalHours = xHours;
        // Format the result as hours:minutes:seconds
        if (xMinutes < 10)
        {
            return $"{xTotalHours}:{"0" + xMinutes}:{"0" + xSeconds}";
        }
        else
        {
            return $"{xTotalHours}:{xMinutes}:{"0" + xSeconds}";
        }
    }
}

Using the function foreach row :

foreach (System.Data.DataRow row in ClsForm.DtGet.Rows)
{
    string xINPUT = row["TOTAL"].ToString();
    string xTOTAL = ClsForm.ConvertToHoursMinutesSeconds(xINPUT);
    row["xTOTAL"] = (xTOTAL);
}

This is my query

SELECT 
SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(ScdEmpOut, ScdEmpIN)))) AS TOTAL,
EmpName AS xTOTAL
FROM tblschedule
INNER JOIN tblemp ON tblschedule.ScdEmpID = tblemp.EmpID

And this is datagridview :

Thanks again for responding and sorry for the inconvenience

Regards,

MUHAMMED DÖNMEZ

[Manikanda Akash Munisamy]

Hi MUHAMMED DÖNMEZ,

You're welcome. Please get back to us if you need any further assistance, we will be happy to help you

Regards,
Manikanda Akash

[MUHAMMED DÖNMEZ]

Sure I will ,, thanks again