DataFormatString for time inside GridView in AM/PM format using C# and VB.Net in ASP.Net

Hiii, I am trying to Bind dyanamic grid and then save it in database and then retrive data from database.

The main problem is to retrive time from database to grid.

From some articles i have read that databse does not understand AM/PM format so i want to save  in  24 Hrs format but while reading it should comes with AM/PM format.

If possible please share sample code.

suggest any changes requires in database.

<asp:GridView ID="grdRoomDetails" runat="server" AutoGenerateColumns="false" CssClass="table table-condensed table-bordered table-hover"
    ShowHeaderWhenEmpty="true" Style="margin-bottom: 0px" RowStyle-Height="15px">
    <Columns>
        <asp:TemplateField HeaderText="Room ID" HeaderStyle-CssClass="bg-info gridHeaders">
            <ItemTemplate>
                <asp:Label ID="lblRoomNum" runat="server" Text='<%# Eval("Room_Num") %>' />
            </ItemTemplate>
            <ItemStyle CssClass="text-center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Check-In Time" HeaderStyle-CssClass="bg-info gridHeaders">
            <ItemTemplate>
                <asp:Label ID="lblCheckInTime" runat="server" Text='<%# Eval("CheckIN_Time","{0:hh:mm:ss}") %>' />
            </ItemTemplate>
            <ItemStyle CssClass="text-center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Check-Out Time" HeaderStyle-CssClass="bg-info gridHeaders">
            <ItemTemplate>
                <asp:Label ID="lblCheckOUTTime" runat="server" Text='<%# Eval("CheckOUT_Time","{0:hh:mm:ss}") %>' />
            </ItemTemplate>
            <ItemStyle CssClass="text-center" />
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<div class="input-group ">
    <asp:TextBox ID="txtCheckINTime" runat="server" Text="10:00 AM" CssClass="form-control"
        data-provide="timepicker" OnTextChanged="txtCheckINTime_TextChanged" data-toggle="tooltip"
        data-placement="top" title="Select Check-In Time"></asp:TextBox>
    <span class="input-group-addon"><i class="glyphicon glyphicon-time"></i></span>
</div>
<script type="text/javascript">
$(function pageLoad(sender, args) {
    $("txtCheckINTime").timepicker({
        showInputs: false
    });
</script>

Blank datasource :

DataTable dt1 = new DataTable();
dt1.Columns.AddRange(new DataColumn[3] { new DataColumn("Room_Num"), new DataColumn("CheckIN_Time", typeof(DateTime)), new DataColumn("CheckOUT_Time", typeof(DateTime)) });
ViewState["BookingDetails"] = dt1;
grdRoomDetails.DataSource = (DataTable)ViewState["BookingDetails"];
grdRoomDetails.DataBind();

Add Data to grid:

DataTable dt1 = (DataTable)ViewState["BookingDetails"];
string CheckInTime = Convert.ToDateTime(this.txtCheckINTime.Text.Trim()).ToString("hh:mm tt");
string CheckOutTime = Convert.ToDateTime(this.txtCheckoutTime.Text.Trim()).ToString("hh:mm tt");

dt1.Rows.Add(drpRoomNum.SelectedItem.Text.Trim(), CheckInTime, CheckOutTime);
ViewState["BookingDetails"] = dt1;
grdRoomDetails.DataSource = (DataTable)ViewState["BookingDetails"];
grdRoomDetails.DataBind();

Save In Databse:

for (int i = 0; i < grdRoomDetails.Rows.Count; i++)
{
    Label lblRoomNum = (Label)grdRoomDetails.Rows[i].Cells[1].FindControl("lblRoomNum");

    Label CheckInTime = (Label)grdRoomDetails.Rows[i].Cells[1].FindControl("lblCheckInTime");

    Label CheckOutTime = (Label)grdRoomDetails.Rows[i].Cells[1].FindControl("lblCheckOutTime");

    con.Open();
    string CheckIn_Time = Convert.ToDateTime(CheckInTime.Text.Trim()).ToString("hh:mm tt");
    string CheckOut_Time = Convert.ToDateTime(CheckOutTime.Text.Trim()).ToString("hh:mm tt");

    string queryInsertBook_Details = "Insert into Book_Details (RoomNum,CheckIn_Time,CheckOut_Time) values ('" + lblRoomNum.Text + "','" + CheckIn_Time + "','" + CheckOut_Time + "')";
    SqlCommand cmdInsertBook_Details = new SqlCommand(queryInsertBook_Details, con);
    cmdInsertBook_Details.ExecuteNonQuery();
    cmdInsertBook_Details.Dispose();
    con.Close();
}

Retrive from Database:

SqlCommand cmd = new SqlCommand("select * from Book_Details where ID='" + SessionBooking_ID + "'", con);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
ViewState["BookingDetails"] = dt;
grdRoomDetails.DataSource = (DataTable)ViewState["BookingDetails"];
grdRoomDetails.DataBind();       

Create table Book_Details 
(
    ID INT IDENTITY (1,1) PRIMARY KEY NOT NULL,
    RoomNum Nvarchar(15) Null,
    CheckIN_Date DATE NOT Null,
    CheckIN_Time Time  Null,
    CheckOUT_Date DATE NOT Null,
    CheckOUT_Time Time  Null
)
Create UNIQUE INDEX [piBook_Details] ON [Book_Details] ([ID] ASC );