在数据库的不同列中减去日期和保存值 [英] subtracting Dates and saving value in different column of database

问题描述

好的，所以我想做的是拿两个日期，减去它们并将它们的答案（整数）插入另一列。应该是15天等等。要获取的日期存储在数据库中。答案需要在DAYS这是我的gridview代码：

Ok, so What i want to do is take Two dates, subtract them and insert their answer (in integers) to another column. Should be like 15 days and so on. The dates to be fetched are stored in the database. The answer needs to be in DAYS this is my gridview code:

<div class="gview"> 
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" HeaderStyle-HorizontalAlign="Center"  FooterStyle-HorizontalAlign="Center" CssClass="gview" DataKeyNames="PID" Width="613px">
        <Columns>
            <asp:BoundField DataField="PID" HeaderText="PID" SortExpression="PID" InsertVisible="False" ReadOnly="True" />
            <asp:BoundField DataField="Pname" HeaderText="Pname" SortExpression="Pname" />
            <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
            <asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department" />
            <asp:BoundField DataField="Ward" HeaderText="Ward" SortExpression="Ward" />
            <asp:BoundField DataField="Bed" HeaderText="Bed" SortExpression="Bed" />
            <asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" />
            <asp:BoundField DataField="AdmitDate" HeaderText="AdmitDate" SortExpression="AdmitDate" />
            <asp:BoundField DataField="DischargeDate" HeaderText="DischargeDate" SortExpression="DischargeDate" />
            <asp:BoundField DataField="NumOfDays" HeaderText="NumOfDays" SortExpression="NumOfDays" />
        </Columns>
        <FooterStyle HorizontalAlign="Center"></FooterStyle>
        <HeaderStyle HorizontalAlign="Center"></HeaderStyle>
    </asp:GridView>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SMCConnectionString %>" SelectCommand="SELECT [PID], [Pname], [Gender], [Department], [Ward], [Bed], [Status], [AdmitDate], [DischargeDate], [NumOfDays] FROM [Patient]">
    </asp:SqlDataSource>
 </div>

这是我的C＃代码：

This is my C# code:

protected void Button6_Click(object sender, EventArgs e)
{

    string str = "update Patient (Department, Ward, Bed, Status, AdmitDate, DischargeDate) set Department= @Department,Ward=@Ward,Bed=@Bed,Status=@Status,AdmitDate=@AdmitDate,DischargeDate=@DischargeDate   where PID = '" + TextBox3.Text + "'";
    cmd = new SqlCommand(str, con);
    con.Open();
    cmd.Parameters.AddWithValue("@Department", DropDownList4.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@Ward", DropDownList3.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@Bed", DropDownList1.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@Status", DropDownList2.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@AdmitDate", TextBox1.Text);
    DateTime.Parse(TextBox1.Text.ToString());
    cmd.Parameters.AddWithValue("@DischargeDate", TextBox2.Text);
    DateTime.Parse(TextBox2.Text.ToString());
    var diff = DateTime.Parse(TextBox2.Text.ToString()).Subtract(DateTime.Parse(TextBox1.Text.ToString()));
    cmd.Parameters.AddWithValue("@NumOfDays", diff);
    cmd.ExecuteNonQuery();
    con.Close();
    show();
    Response.Write("Record Updated");
}

Now when I click the button it generates the following error:

    SqlDbType.Time overflow. Value '10.00:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999.

I did quite a research and found out the idea that i have implemented. But it's no good so far. Any kind of help is greatly appreciated.

附加信息：存储日期的两列的类型都设置为DateTime，这些值是我正在插入。 prntscr.com/666tl8（使用日历）。 NumOfDays的DataType是int



非常感谢任何帮助！

ADDITIONAL INFO: The type of both columns that store Dates is set to DateTime and these are the values that i am inserting. prntscr.com/666tl8 (using a Calender). DataType of NumOfDays is int

any help is greatly appreciated!

推荐答案

ConnectionStrings：SMCConnectionString％> SelectCommand = SELECT [PID]，[Pname]，[Gender]，[Department]，[Ward]，[Bed]，[Status]，[AdmitDate]，[DischargeDate]，[NumOfDays]来自[患者] >
< / asp：SqlDataSource >
< < span class =code-leadattribute> / div >

ConnectionStrings:SMCConnectionString %>" SelectCommand="SELECT [PID], [Pname], [Gender], [Department], [Ward], [Bed], [Status], [AdmitDate], [DischargeDate], [NumOfDays] FROM [Patient]"> </asp:SqlDataSource> </div>

这是我的C＃代码：

This is my C# code:

protected void Button6_Click(object sender, EventArgs e)
{

    string str = "update Patient (Department, Ward, Bed, Status, AdmitDate, DischargeDate) set Department= @Department,Ward=@Ward,Bed=@Bed,Status=@Status,AdmitDate=@AdmitDate,DischargeDate=@DischargeDate   where PID = '" + TextBox3.Text + "'";
    cmd = new SqlCommand(str, con);
    con.Open();
    cmd.Parameters.AddWithValue("@Department", DropDownList4.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@Ward", DropDownList3.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@Bed", DropDownList1.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@Status", DropDownList2.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@AdmitDate", TextBox1.Text);
    DateTime.Parse(TextBox1.Text.ToString());
    cmd.Parameters.AddWithValue("@DischargeDate", TextBox2.Text);
    DateTime.Parse(TextBox2.Text.ToString());
    var diff = DateTime.Parse(TextBox2.Text.ToString()).Subtract(DateTime.Parse(TextBox1.Text.ToString()));
    cmd.Parameters.AddWithValue("@NumOfDays", diff);
    cmd.ExecuteNonQuery();
    con.Close();
    show();
    Response.Write("Record Updated");
}

Now when I click the button it generates the following error:

    SqlDbType.Time overflow. Value '10.00:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999.

I did quite a research and found out the idea that i have implemented. But it's no good so far. Any kind of help is greatly appreciated.

附加信息：存储日期的两列的类型都设置为DateTime，这些值是我正在插入。 prntscr.com/666tl8（使用日历）。 NumOfDays的DataType是int



非常感谢任何帮助！

ADDITIONAL INFO: The type of both columns that store Dates is set to DateTime and these are the values that i am inserting. prntscr.com/666tl8 (using a Calender). DataType of NumOfDays is int

any help is greatly appreciated!

在得到日期之间的差异之前，你需要查看如何填充存储过程参数。



存储的proc期望DateTimes，你传递字符串。我无法想象这会起作用，但即使它容易受到地区问题的影响。不同的地方以不同的方式表示日期。



如果您使用类似DatePicker控件而不是文本框的东西，它将为您提供一个很好的区域安全DateTime来处理而不是字符串可以是任何东西。



现在为差异部分。良好的SQL实践不会存储差异，您可以动态计算它。通过存储它，您可以使数据库容易受到错误数据的影响有人可以更新其中一个日期或差异并导致不一致。



如果你不存储它，你可以有一个提供它的视图。在TSQL中，您可以简单地从另一个日期中减去一个日期，以获得浮点形式的天数差异，您可能希望将其舍入。

Well before getting to the difference between the dates, you need to look at how you are populating your stored procedure parameters.

The stored proc is expecting DateTimes, you are passing strings. I cant imagine this would work, but even if it does its vulnerable to regional issues. Different places represent dates differently.

If you use something like a DatePicker control rather than a textbox, it will give you a nice region safe DateTime to work with rather than a string that could be anything.

Now for the difference part. Good SQL practice would not store the difference, you can calculate it on the fly. By storing it you leave the database vulnerable to bad data. Someone could update one of the dates or the difference and lead to inconsistency.

If you don't store it, you could have a view which provides it. In TSQL you can simply subtract one date from another to get the difference in days in floating point form, which you probably want to round up.

这篇关于在数据库的不同列中减去日期和保存值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！