?
Solved

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

Posted on 2016-08-17
6
Medium Priority
?
96 Views
Last Modified: 2016-09-05
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
0
Comment
Question by:Joven Canezal
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:it_saige
ID: 41759325
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-
0
 

Author Comment

by:Joven Canezal
ID: 41759349
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/
0
 
LVL 35

Accepted Solution

by:
it_saige earned 2000 total points (awarded by participants)
ID: 41759384
Are you using SQL or MySql?

MySql does not require a datepart for the calculation: http://www.w3schools.com/sql/func_datediff_mysql.asp

Try:
command = new MySqlCommand("Select DATEDIFF(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-
0
 

Author Comment

by:Joven Canezal
ID: 41759450
THANKS SIR! Got it :D seems fine now, I'm using mysql thanks for your tips
0
 
LVL 35

Expert Comment

by:it_saige
ID: 41784463
Answered authors question.

-saige-
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question