Solved

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

Posted on 2016-08-17
6
32 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
6 Comments
 
LVL 32

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 32

Accepted Solution

by:
it_saige earned 500 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 32

Expert Comment

by:it_saige
ID: 41784463
Answered authors question.

-saige-
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now