[已解决]到数据库中日期之间的日期差 [英] [Solved] date difference between to dates from database

问题描述

我有一个数据网格视图，使用数据表填充它.

进行一些数学计算，但是我希望在此代码行中将日期之间的差异包括在我的计算中:

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"));

我希望将"5"替换为与日期之间的差异值.

如果可能的话，我希望考虑当日之间的差异.例如今天是27.03.2011，第二天28.03.2011结果2天.

谢谢.



这是al代码:

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.

推荐答案

如果要获取SQL语句中的差异，可以使用DATEDIFF [ ^ ].例如，以下内容获取了今天和明天之间的天数差异:

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:

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

您可以按照Mika的建议在数据库端执行此操作，也可以根据需要在TableNewRow或RowChanged事件中手动进行操作.在事件处理程序中，手动进行差异处理，然后添加一个以在结果中包括日期之一.

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.

我被取消，我创建了一个litle类

I rezolved, i create a litle class

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()));
            }

我希望以后能正常工作，我是C#的新人，

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

这篇关于[已解决]到数据库中日期之间的日期差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！