Sana Qureshi Sana Qureshi - 5 months ago 17
SQL Question

Getting Date only instead of date and time

Ok so i have sorted the difference of Dates, it's working great. The problem i am facing now is that when i click on

date
from the Calender (AJAX calender that appears when you click on
TextBox
) It adds
Date
along with the
null time
to database. I only want
Date
not
Time
.

Please note: that the
DataType
of
AdmitDate
and
DischargeDate
is set to
datetime
.

Here is my C# code:

string str = "update Patient set Department= @Department,Ward=@Ward,Bed=@Bed,Status=@Status,AdmitDate=@AdmitDate,DischargeDate=@DischargeDate, NumOfDays=@NumOfDays 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 AdmitDate = DateTime.Parse(TextBox1.Text);
// AdmitDate.ToString("d");
AdmitDate.ToShortDateString();
cmd.Parameters.AddWithValue("@DischargeDate", TextBox2.Text);
DateTime DischargeDate = DateTime.Parse(TextBox2.Text);
// DischargeDate.ToString("d");
DischargeDate.ToShortDateString();
var diff = (DischargeDate - AdmitDate).Days;
cmd.Parameters.AddWithValue("@NumOfDays", diff);
cmd.ExecuteNonQuery();
con.Close();


As you can see i tried 2 methods, they don't give any error but don't work the way they are supposed to. I tried some other things as well (mentioned here and on other sites) but they didn't work either. Any kind of help will be appreciated.
Additional Info: I am using SQL server 2008 R2 with VS2013.

EDIT: here is my SQL table structure:

CREATE TABLE [dbo].[Patient] (
[PID] INT IDENTITY (1, 1) NOT NULL,
[Department] NVARCHAR (50) NULL,
[Ward] NVARCHAR (50) NULL,
[Bed] NVARCHAR (50) NULL,
[Status] NVARCHAR (50) NULL,
[AdmitDate] DATETIME NULL,
[DischargeDate] DATETIME NULL,
[NumOfDays] INT NULL,
CONSTRAINT [PK__Patient__C57755200BC6C43E] PRIMARY KEY CLUSTERED ([PID] ASC)


i have removed the fields that are not relevant.

EDIT: my gridview code:

<div id="mbody"> <br />
<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:CommandField ShowDeleteButton="True" />
<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>







<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SMCConnectionString %>" SelectCommand="SELECT [PID], [Pname], [Gender], [Department], [Ward], [Bed], [Status], [AdmitDate], [DischargeDate], [NumOfDays] FROM [Patient]" DeleteCommand="DELETE FROM [Patient] WHERE [PID] = @PID" InsertCommand="INSERT INTO [Patient] ([Pname], [Gender], [Department], [Ward], [Bed], [Status], [AdmitDate], [DischargeDate], [NumOfDays]) VALUES (@Pname, @Gender, @Department, @Ward, @Bed, @Status, @AdmitDate, @DischargeDate, @NumOfDays)" UpdateCommand="UPDATE [Patient] SET [Pname] = @Pname, [Gender] = @Gender, [Department] = @Department, [Ward] = @Ward, [Bed] = @Bed, [Status] = @Status, [AdmitDate] = @AdmitDate, [DischargeDate] = @DischargeDate, [NumOfDays] = @NumOfDays WHERE [PID] = @PID">
<DeleteParameters>
<asp:Parameter Name="PID" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="Pname" Type="String" />
<asp:Parameter Name="Gender" Type="String" />
<asp:Parameter Name="Department" Type="String" />
<asp:Parameter Name="Ward" Type="String" />
<asp:Parameter Name="Bed" Type="String" />
<asp:Parameter Name="Status" Type="String" />
<asp:Parameter Name="AdmitDate" Type="DateTime" />
<asp:Parameter Name="DischargeDate" Type="DateTime" />
<asp:Parameter Name="NumOfDays" Type="Int32" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="Pname" Type="String" />
<asp:Parameter Name="Gender" Type="String" />
<asp:Parameter Name="Department" Type="String" />
<asp:Parameter Name="Ward" Type="String" />
<asp:Parameter Name="Bed" Type="String" />
<asp:Parameter Name="Status" Type="String" />
<asp:Parameter Name="AdmitDate" Type="DateTime" />
<asp:Parameter Name="DischargeDate" Type="DateTime" />
<asp:Parameter Name="NumOfDays" Type="Int32" />
<asp:Parameter Name="PID" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>

</div>

Answer

From what I understand you want to save up the only date part of the datetime you can do that as below:

    DateTime AdmitDate = DateTime.Parse(TextBox1.Text);
    cmd.Parameters.AddWithValue("@AdmitDate", AdmitDate.ToShortDateString());
    DateTime DischargeDate = DateTime.Parse(TextBox2.Text);
    cmd.Parameters.AddWithValue("@DischargeDate", DischargeDate.ToShortDateString());

In your code you are not assigning the ToShortDateString() value to anything that's why the changes aren't reflecting.

And you might want to change your query to use it parametrized values to avoid SQL injection. Here is more info on it

string str = "update Patient set Department = @Department, Ward = @Ward, Bed = @Bed, Status = @Status, AdmitDate = @AdmitDate, DischargeDate = @DischargeDate, NumOfDays = @NumOfDays where PID = @PID";
cmd.Parameters.AddWithValue("@PID", TextBox3.Text);

UPDATE

As from your comments it is clear that the date you are showing up in the UI is showing time because you need to make sure that the value is converted to to only date before setting it to the UI control.

OR

If you have full control of the tables and you never ever want to store time then the ultimate solution would be Change the DateTime to the Date.

In this case to when you fetch it and if you store it in the DateTime type variable it will add the Time part to it. So you have to apply the .ToShortDateString() before assigning it to the UI control.

UPDATE

TO change the UI you need to put the DataformatString property of the boundfield of the date columns. In your gridview for these 2 lines add the DataformatString property with the value {0:MM/dd/yyyy}

    <asp:BoundField DataField="AdmitDate" HeaderText="AdmitDate" SortExpression="AdmitDate" DataFormatString="{0:MM/dd/yyyy}"/>
    <asp:BoundField DataField="DischargeDate" HeaderText="DischargeDate" SortExpression="DischargeDate" DataFormatString="{0:MM/dd/yyyy}"/>