Getting Date only instead of date and time

Question

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 1

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}"/>

Answer 2

DateTime AdmitDate = DateTime.Parse(TextBox1.Text);

string admitDate = AdmitDate.Date; // it gives you only Date

If you want only Date in SQL SERVER means you need to change the datatype of the column from datetime to date.

Answer 3

Accordimg to :

the DataType of AdmitDate and DischargeDate is set to datetime.

you can't insert only date-part to these column.if you tried then it'll automatically save the time as a 12:00 AM(starting time of the day).

if you really want to insert only date then change the column datatype to date.

ALTER TABLE table_name
ALTER COLUMN column_name datatype

See:Alter_Table_datatype

Answer 4

If you want only date part to be stored in DB then use datatype of column as date.

For example -

DECLARE @date1 date= '02-24-15 23:20:51'; -- will take only the date  
SELECT @date1 AS 'date1'

Will have -

date1
--------------
2015-02-24

EDIT-

From your edition and of table structure I ran simple test and it is working fine.

CREATE TABLE #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]     Date       NULL,
[DischargeDate] Date       NULL,
[NumOfDays]     INT            NULL
);

Insert into #Patient Values('ABC','Ward1','Bed2','Active','02-24-15 23:20:51','02-26-15 23:20:51',2);

Select * from #Patient;

Results-

PID         Department                                         Ward                                               Bed                                                Status                                             AdmitDate  DischargeDate NumOfDays
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------- ------------- -----------
1           ABC                                                Ward1                                              Bed2                                               Active                                             2015-02-24 2015-02-26    2

If you check I modified datetime data type to date

    [AdmitDate]     Date       NULL,
    [DischargeDate] Date       NULL,