[Solved] date difference between to dates from database

Question

4.00/5 (2 votes)

See more:

I have a datagrid view, a use datatable to populate it.

make some math calculation, but i whant to include in my calculation a difference between dates, in this line of code:

Table.Columns.Add(new DataColumn("DePlataAzi", typeof(Decimal), "((Total_Platit * (Procent/100)) * 5) + Total_Platit"));

I whant to replace the "5" with the value of diffence betwenn to dates.

If posible in the difference between dates i whant take into consideration the current day. For example today is 27.03.2011, next day 28.03.2011 result 2 day.

thanks.

This is the al code:

dataContracteClienti.Rows.Clear();
            Program.Connection.CommandText = "select Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent, sum(ContractItems.Payment) AS Total_Platit from Contracts INNER JOIN ContractItems ON Contracts.ContractId = ContractItems.ContractId WHERE ClientID=@ClientID "
                + "GROUP BY Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent ORDER BY Contracts.StartDate";
            Program.Connection.AddParameter("@ClientID", cboNumeClient.SelectedValue.ToString());                    
            DataTable Table = new DataTable();
            Program.Connection.FillDataTable(Table, true);
       

            Table.Columns.Add(new DataColumn("DePlataAzi", typeof(Decimal), "((Total_Platit * (Procent/100)) * 5) + Total_Platit"));
            Table.Columns.Add(new DataColumn("DePlataLaTermen", typeof(Decimal), "((Total_Platit * (Procent/100)) * 20) + Total_Platit"));
            

            foreach (DataRow Row in Table.Rows)
            {
                dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToInt32(Row["Total_Platit"].ToString()), Convert.ToDecimal(Row["DePlataAzi"].ToString()), Convert.ToDecimal(Row["DePlataLaTermen"].ToString()));
            }

UPDATE:
Resolved by OP and posted as an answer.

Posted 27-Mar-11 7:33am

aciobanita

Updated 27-Mar-11 9:26am

Sandeep Mewara

v4

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-03-27T07:44:00

Solution 1

If you want to fetch the difference in the SQL statement, you can use DATEDIFF[^]. For example the following gets the difference between today and tomorrow in days:

SQL

SELECT DATEDIFF(day, GETDATE(), DATEADD(day, 1, GETDATE()))

Posted 27-Mar-11 7:44am

Wendelius

Comments

aciobanita 27-Mar-11 13:52pm

i try this

Program.Connection.CommandText = "select Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent, sum(ContractItems.Payment) AS Total_Platit, DATEDIFF (day, Contracts.StartDate, Contracts.EndDate) AS ShippingDays from Contracts INNER JOIN ContractItems ON Contracts.ContractId = ContractItems.ContractId WHERE ClientID=@ClientID "
+ "GROUP BY Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent ORDER BY Contracts.StartDate";

error: No value given for one or more required parameters.

Wendelius 27-Mar-11 13:59pm

The error is something different. Most likely you haven't given a value for @ClientID or the SqlDbType is wrong. Debug the portion where you add the parameter to the command to see if it's correctly.

aciobanita 27-Mar-11 14:05pm

if i remouve the DATEDIFF (day, Contracts.StartDate, Contracts.EndDate) AS ShippingDays, no error

Wendelius 27-Mar-11 14:12pm

That's weird, tested with several variations and always working fine. Can you take the statement to Sql Server Management Studio and test the same statement there?

Costica U 27-Mar-11 14:33pm

Data type for Contracts.StartDate and Contracts.EndDate must be DATETIME.
If not use DATEDIFF (day, CONVERT(DATETIME,Contracts.StartDate), CONVERT(DATETIME,Contracts.EndDate)) AS ShippingDays

Wendelius 27-Mar-11 14:55pm

Yes the datatype should be date or implicit conversion available. For example:

SELECT DATEDIFF(day, '20110323', GETDATE())

works fine (of course depending on the date format settings). But still the error for the conversion failure is different. For example:

SELECT DATEDIFF(day, 'ABC', GETDATE()) gives Conversion failed when converting date and/or time from character string.. So still confused. Unless this is Access. In that case the day should be replaced with "d".

*dan!sh* · Answer 2 · 2011-03-27T07:53:00

Solution 2

You can either do this at the database end as Mika suggested or you can do it manually in the TableNewRow or RowChanged event as appropriate. In the event handler, manually do the difference and then add one to include one of the dates in the result.

Posted 27-Mar-11 7:53am

dan!sh

aciobanita · Answer 3 · 2011-03-27T09:19:00

I rezolved, i create a litle class

C#

using System;
using System.Collections.Generic;
using System.Text;
namespace CalculareDiferenta
{
    public class DiferentaData
    {
        private int Zi;
        public DiferentaData(DateTime d1, DateTime d2)
        {
            TimeSpan ts = d1.Subtract(d2);
            Zi = ts.Days;

        }
        public override string ToString()
        {
            return this.Zi.ToString();
        }
        public int Zile
        {
            get
            {
                return this.Zi;
            }
        }
    }
}

and the finall cod look like this

dataContracteClienti.Rows.Clear();
            Program.Connection.CommandText = "select Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent, sum(ContractItems.Payment) AS Total_Platit from Contracts INNER JOIN ContractItems ON Contracts.ContractId = ContractItems.ContractId WHERE ClientID=@ClientID "
                + "GROUP BY Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent ORDER BY Contracts.StartDate";
            Program.Connection.AddParameter("@ClientID", cboNumeClient.SelectedValue.ToString());                    
            DataTable Table = new DataTable();
            Program.Connection.FillDataTable(Table, true);

            DiferentaData DiferentaDePlataLaTermen = new DiferentaData(Convert.ToDateTime(Table.Rows[0]["EndDate"].ToString()), Convert.ToDateTime(Table.Rows[0]["StartDate"].ToString()));
            DiferentaData DiferentaDePlataAzi = new DiferentaData(Convert.ToDateTime(DateTime.Now.ToShortDateString()), Convert.ToDateTime(Table.Rows[0]["StartDate"].ToString()));

            Table.Columns.Add(new DataColumn("DePlataLaTermen", typeof(Decimal), "((Total_Platit * (Procent/100)) * (" + DiferentaDePlataLaTermen.ToString() + "+ 1 )) + Total_Platit"));
            Table.Columns.Add(new DataColumn("DePlataAzi", typeof(Decimal), "((Total_Platit * (Procent/100)) * (" + DiferentaDePlataAzi + " +1 )) + Total_Platit"));

            foreach (DataRow Row in Table.Rows)
            {
                
                dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToInt32(Row["Total_Platit"].ToString()), Convert.ToDecimal(Row["DePlataAzi"].ToString()), Convert.ToDecimal(Row["DePlataLaTermen"].ToString()));
            }

I hope in the future to work ok, i'm new in C#,