locked
Calculating Time Values RRS feed

  • Question

  • User-347359328 posted

    Hello,

     I'm creating a time card web app, and I'd appreciate advice for handling the time values in my database.  I'm thinking I should store the military time entries as an nvarchar and work with them as a string.  I would split the string using the colon as the special character, but I don't how to go about getting the values from a dataset in a way that I could use arithmatic on them.  Below is my crude idea, and any help is appreciated!

    string select = "select TIME_IN, TIME_OUT from dbo.TIME;";

    Dataset ds = new Dataset();

    ds = Timecard.SqlQuery(select);

    string[] strTimeIn = new string[?];
       
    char[] splitter  = {';'};
                
    strTimeIn = ds.Tables[0].Rows[0][0].Split(splitter);

    Obviously this isn't anything workable, but that's where I'm at so far.  Thanks.

    Tuesday, January 27, 2009 11:37 PM

Answers

  • User-1342395089 posted

    Hello Diseirra,

    I want to calculate total hours and minutes from the TIME_IN and TIME_OUT data.  I would like to have a sum total of hours and minutes, by subtracting TIME_IN from TIME_OUT and += them through all the entries in the database.  Could you show me how to make this happen?

     Thank you.

    okay, then why dont you use the inbuilt function of sql??/

    sum(time_in) and sum(time_out) will serve your purpose.

    also sum((time_in)-(time_out)) as total, given that time_in and time_out are of same datatype(not varchar)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 29, 2009 12:42 AM
  • User297557456 posted

    Hi,

    try this query

    Select DateDiff(hh,Time_In,Time_out)

    for E.g. Select DateDiff(hh,'02-07-1987',GetDate())

    returns 22 as "hh" denotes Hours

    for more output types review this link

    http://www.pctools.com/guides/scripting/detail/41/?act=reference

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 4, 2009 5:47 PM

All replies

  • User-1342395089 posted

    hi

    you already have time_in and time_out, why do you want to split the columns?

    what exactly is the format of the output you want?

    if u are sure wat u have been duing, ten you need to use some loop tp parse the column values of the dataset.

    Wednesday, January 28, 2009 2:39 AM
  • User-347359328 posted

    Hello Diseirra,

    I want to calculate total hours and minutes from the TIME_IN and TIME_OUT data.  I would like to have a sum total of hours and minutes, by subtracting TIME_IN from TIME_OUT and += them through all the entries in the database.  Could you show me how to make this happen?

     Thank you.

    Wednesday, January 28, 2009 12:29 PM
  • User-1342395089 posted

    Hello Diseirra,

    I want to calculate total hours and minutes from the TIME_IN and TIME_OUT data.  I would like to have a sum total of hours and minutes, by subtracting TIME_IN from TIME_OUT and += them through all the entries in the database.  Could you show me how to make this happen?

     Thank you.

    okay, then why dont you use the inbuilt function of sql??/

    sum(time_in) and sum(time_out) will serve your purpose.

    also sum((time_in)-(time_out)) as total, given that time_in and time_out are of same datatype(not varchar)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 29, 2009 12:42 AM
  • User-347359328 posted

     What datatype should I use?  Sum will not work with datetime data type and obviously not varchar.  The time is input by HH:MM.  I assume I need to split the values from the colon to work with the numbers.

    Wednesday, February 4, 2009 4:44 PM
  • User-347359328 posted

    Below is what I have in mind.  I know it does not work.  I know this isn't anything difficult for someone who knows what they're doing.  I just want to calculate total time from clocking in and clocking out.  Help please. 

    private void TimeCalc()
        {
            string select = "select TimeIn, TimeOut from dbo.TIME;";
            DataSet ds = new DataSet();
            ds = Timecard.SqlQuery(select);

            ArrayList alIn = new ArrayList();
            ArrayList alOut = new ArrayList();
            DataTable tbl = ds.Tables[0];
            for (int i = 0; i < tbl.Rows.Count; i++)
            {
                DataRow myRow = tbl.Rows[i];
                char[] splitter = { ':' };
                string strTimeIn = (string)myRow["TimeIn"];
                alIn.Add(strTimeIn.Split(splitter)); // WHERE DO I PUT THE SPLIT DATA? IT NEEDS TO BE SEPARATE FOR CALCULATION OF HOURS/MINUTS
                string strTimeOut = (string)myRow["TimeOut"];
                alOut.Add(strTimeOut.Split(splitter));
            }
            for (int i = 0; i < alOut.Count; i++)
            {
                int totaltime = Convert.ToInt32(alOut[i]) - Convert.ToInt32(alIn[i]); // HOW DO I DEAL TYPE CASTING?
                lblTotalTime.Text = totaltime.ToString();
            }
        }

    Wednesday, February 4, 2009 5:28 PM
  • User297557456 posted

    Hi,

    try this query

    Select DateDiff(hh,Time_In,Time_out)

    for E.g. Select DateDiff(hh,'02-07-1987',GetDate())

    returns 22 as "hh" denotes Hours

    for more output types review this link

    http://www.pctools.com/guides/scripting/detail/41/?act=reference

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 4, 2009 5:47 PM
  • User-347359328 posted

     Thanks.  The DateDiff function gave me a way to figure the time values in hours.  Unfortunately, it doesn't do what I asked, which is calculating time values in C# and working with the dataset.  I will mark this as answer.

    Saturday, February 21, 2009 6:03 PM