Avatar of Joven Canezal
Joven Canezal
 asked on

C# - Payroll/Getting Hours Worked thru time difference.

Hello, I am working on a school project, it's a payroll system.
I have this current code that gets the difference of the TIME IN AND TIME OUT Of the user.

 try
{
    connection = new MySqlConnection(source);
    command = new MySqlCommand("Select DATEDIFF(TimeIN) - DATEDIFF(TimeOUT) FROM payroll.attendance WHERE Date BETWEEN '" + datefrom.Value.ToString("yyyy-MM-dd") + "' AND '" + dateto.Value.ToString("yyyy-MM-dd") + "' AND EmployeeID='" + lblid.Text + "'", connection);

    connection.Open();
    MySqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        string diff = reader.GetString("DATEDIFF").ToString();
        MessageBox.Show(diff.ToString());
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Open in new window


The code is not yet working it says "incorrect parameter count in the call to native function datediff"

Another problem is that, It must get the time difference of all the dates under the user input from the datepicker.
So its like this "SELECT DATEDIFF FROM MYDATEBASE WHERE Date Between 08-10-2016 AND 08-17-2016"
From this, it must get all the timedifference of the date between the user input.
Heres the sample of my database(attendance)

Screenshot_20.png
MySQL ServerC#

Avatar of undefined
Last Comment
it_saige

8/22/2022 - Mon
it_saige

You are getting an error because DATEDIFF returns the difference between two fields:

https://msdn.microsoft.com/en-us/library/ms189794.aspx

The proper form for DATEDIFF is:
DATEDIFF ( datepart , startdate , enddate )

Open in new window

In your case it appears as if you want to use something like:
command = new MySqlCommand("Select DATEDIFF(minute, TimeIN, TimeOUT) FROM payroll.attendance WHERE Date BETWEEN '" + datefrom.Value.ToString("yyyy-MM-dd") + "' AND '" + dateto.Value.ToString("yyyy-MM-dd") + "' AND EmployeeID='" + lblid.Text + "'", connection);

Open in new window


-saige-
Joven Canezal

ASKER
it_saige, I already tried that. but as stated above I get this error "incorrect parameter count in the call to native function datediff" I retried that code and still the same/
ASKER CERTIFIED SOLUTION
it_saige

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Joven Canezal

ASKER
THANKS SIR! Got it :D seems fine now, I'm using mysql thanks for your tips
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
it_saige

Answered authors question.

-saige-