How to format Time in GridDateTimeColumn?

[Oleg]

Hi,

I am using few time(7) columns in my SQL table. RadGrid always show the time in GridDateTimeColumn as 13:55:24.1658985 does not matter what I am putting in DataFormatString. I want to show short 12hr notation: "1:55pm". How to do that?

<telerik:GridDateTimeColumn DataField="TicketEmailTime" DataFormatString="{0:t}" FilterControlAltText="Filter colTicketEmailTime column" HeaderText="Ticket Email Time" UniqueName="colTicketEmailTime" DataType="System.DateTime" PickerType="TimePicker" AllowFiltering="False" AllowSorting="False">
    <ColumnValidationSettings>
        <ModelErrorMessage Text="" />
    </ColumnValidationSettings>
</telerik:GridDateTimeColumn>

Regards,
  Oleg

[Shinu]

Hi Oleg,

Your code works fine at my end. Can you try setting the format as 0:HH:mm:ss:

ASPX:

<telerik:GridDateTimeColumn DataField="OrderDate" DataFormatString="{0:HH:mm:ss}" FilterControlAltText="Filter OrderDate column" HeaderText="Order Date Time" UniqueName="OrderDate" DataType="System.DateTime" PickerType="TimePicker" >
</telerik:GridDateTimeColumn>

If this doesn't help, please provide your full code snippet.

Thanks,
Shinu

[Oleg]

I got the exception "Input string was not in a correct format." 
This is how I query data:

DataTable dt = new DataTable();

using (SqlConnection con = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("usp_GetData", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ExecutionDate", RadSelectToDoDate.SelectedDate);

        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            con.Open();
            sda.SelectCommand = cmd;
            sda.Fill(dt);
            return dt;
        }
    }
}

Regards,
  Oleg

[Shinu]

Hi Oleg,

Make sure that your column is of DateTime datatype. Below is a sample code snippet that i tried to fill the radgrid with the value selected from a dropdownlist. Please provide your full code snippet if this doesn't help:

ASPX:

<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1"
    DataTextFormatString="{0:t}" DataTextField="OrderDate" DataValueField="OrderDate"
    AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
</asp:DropDownList>
<telerik:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
    AllowMultiRowSelection="true" OnNeedDataSource="RadGrid1_NeedDataSource">
    <MasterTableView>
        <Columns>
            <telerik:GridDateTimeColumn DataField="OrderDate" FilterControlAltText="Filter OrderDate column"
                DataFormatString="{0:t}" HeaderText="OrderDate Time" UniqueName="OrderDate" PickerType="TimePicker">
            </telerik:GridDateTimeColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

C#:

bool IsSelected = false;

protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
   cmd.Connection = conn;
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = "StoredProcedure2";

   if (IsSelected)
   {
       cmd.Parameters.AddWithValue("@OrderDate", DropDownList1.SelectedItem.Text);
   }
   else
   {
       cmd.Parameters.AddWithValue("@OrderDate", 4);
   }

   DataSet dt = new DataSet();
   SqlDataAdapter adp = new SqlDataAdapter();
   conn.Open();
   adp.SelectCommand = cmd;
   adp.Fill(dt);
   RadGrid1.DataSource = dt;
   conn.Close();
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
   IsSelected = true;
   RadGrid1.Rebind();
}

Thanks,
Shinu

[Oleg]

Thank you for answers but my issue is all MS SQL Table columns are time(7). RadGrid shows time in  13:55:24.1658985  format instead short notation 13:55pm. {0:t} does not work. "{0:HH:mm:ss}" throws the exception "Input string was not in a correct format." I am using few RadTimePicker controls on my form outside of RadGrid and they are working just fine with time(7) SQL columns just RadGridDataTime column does not show time properly.

Regards,
  Oleg

[Vasil]

Hi Oleg,

The MS SQL time object will map to C# TimeSpan not to DateTime.
Since it is a TimeSpan you should use proper format for it. See this article:
http://msdn.microsoft.com/en-us/library/ee372286%28v=vs.110%29.aspx

Regards,
Vasil
Telerik

[Oleg]

Thank you for response. I was moved away from time(7). That was fixed my issue.

Regards,
  Oleg

[Archana]

GridBoundColumn with 24hrs timepicker, In my database i am using time(7), while editing Iam geeting error: Error while setting property 'column_name': 'Cannot convert the value of parameter 'column_name' to the type 'System.TimeSpan'.'.

[Vasil]

Hello Archana,

The error that you are getting is probably caused by wrong configuration of the update parameter of your DataSource. But without additional information it is hard to confirm.

If you show us the declaration of the Grid and DataSource in your page, we could investigate it further.

Regards,
Vasil
Telerik

[Archana]

Hi ,

<telerik:GridDateTimeColumn CurrentFilterFunction="GreaterThanOrEqualTo" HeaderText="Start_Time" DataField="Start_Time" DataType="System.DateTime" EditDataFormatString="HH:mm:ss" FilterControlAltText="Filter Start_Time column" FilterDateFormat="HH:mm:ss" PickerType="TimePicker" UniqueName="Shift_Start_Time">
                    </telerik:GridDateTimeColumn>

SQL database binding with entity framework, I have start_time column with datatype= time(7) , if I use datetime as datatype I can able to edit and save, but when I use system.timespan in radgrid or time(7)in database table. I am facing this issue.

I want to know for inserting and updating a time 24hrs format what can I do, using radgrid and entity framework.

[Vasil]

Hello,

Could you try to use TemplateColumn with RadTimePicker as shown in the first grid of this demo:
https://demos.telerik.com/aspnet-ajax/datetimepicker/functionality/data-binding/defaultcs.aspx
Meanwhile we will debug the issue with GridDateTimeColumn to see what is causing it.

Regards,
Vasil
Telerik

[Archana]

Thanks vasil for your support,

I tried that demo link which you have sent. but while updating and adding new record I'm facing issue.

find the codes below,

<telerik:RadGrid ID="RadGrid1" runat="server" AllowAutomaticDeletes="True" AllowAutomaticInserts="True" AllowAutomaticUpdates="True" AllowPaging="True" AutoGenerateDeleteColumn="True" AutoGenerateEditColumn="True" DataSourceID="EntityDataSource1">

<GroupingSettings CollapseAllTooltip="Collapse all groups"></GroupingSettings>

            <ExportSettings ExportOnlyData="True" IgnorePaging="True">

            </ExportSettings>

            <MasterTableView AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="EntityDataSource1" EditMode="InPlace" CommandItemDisplay="Top">

                <CommandItemSettings ShowExportToExcelButton="True" />

                <Columns>

                    <telerik:GridBoundColumn DataField="ID" DataType="System.Int64" FilterControlAltText="Filter ID column" HeaderText="ID" ReadOnly="True" SortExpression="ID" UniqueName="ID">

                    </telerik:GridBoundColumn>

                    <telerik:GridBoundColumn DataField="Shift_Name" FilterControlAltText="Filter Shift_Name column" HeaderText="Shift_Name" SortExpression="Shift_Name" UniqueName="Shift_Name">

                    </telerik:GridBoundColumn>                          

                       

                     <telerik:GridDateTimeColumn CurrentFilterFunction="GreaterThanOrEqualTo" HeaderText="Start_Time" DataField="Start_Time" DataType="System.DateTime" EditDataFormatString="HH:mm:ss" FilterControlAltText="Filter Start_Time column" FilterDateFormat="HH:mm:ss" PickerType="TimePicker" UniqueName="Shift_Start_Time">
                    </telerik:GridDateTimeColumn>
                      <telerik:GridDateTimeColumn CurrentFilterFunction="GreaterThanOrEqualTo" HeaderText="End_Time" DataField="End_Time" DataType="System.DateTime" EditDataFormatString="HH:mm:ss" FilterControlAltText="Filter End_Time column" FilterDateFormat="HH:mm:ss" PickerType="TimePicker" UniqueName="End_Time">
                    </telerik:GridDateTimeColumn>

                                                                                     

                    <telerik:GridCheckBoxColumn DataField="IsActive" FilterControlAltText="Filter IsActive column" HeaderText="IsActive" SortExpression="IsActive" UniqueName="IsActive">

                    </telerik:GridCheckBoxColumn>

                </Columns>

            </MasterTableView>

        </telerik:RadGrid>

        <asp:EntityDataSource ID="EntityDataSource1" runat="server" ConnectionString="name=dbEntities" DefaultContainerName="dbEntities" EnableDelete="True" EnableFlattening="False" EnableInsert="True" EnableUpdate="True" EntitySetName="Shift" EntityTypeFilter="Shift">

        </asp:EntityDataSource>

        <telerik:RadScriptManager ID="RadScriptManager1" Runat="server">

        </telerik:RadScriptManager>

I have datatype for start_time and end_time as time(7) in my database. Here If I click to edit it is showing  Error while setting property 'Shift_End_Time': 'Cannot convert the value of parameter 'Shift_End_Time' to the type 'System.TimeSpan'.'

Also I want to add other column values in the same record while I am editing( like last_updated_time),but I don't want that column to be displayed in grid, by codebehind/some solutions to save the other columns in database,just to be happen same while updating and adding new records it should automatically save last_updated_time.

 

Thanks,

Archie

 

[Vasil]

Hi Archana,

Did you tried the TemplateColumn instead of DateTimeColumn:

<telerik:GridTemplateColumn HeaderText="Additional times" UniqueName="TemplateColumn1">
    <ItemTemplate>
        <asp:Label runat="server" ID="time" Text='<%# Eval("Shift_End_Time") %>'>
        </asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
        <telerik:RadTimePicker UseTimeSpanForBinding="true" ID="picker2" runat="server" DbSelectedDate='<%# Bind("Shift_End_Time") %>' >
        </telerik:RadTimePicker>
    </EditItemTemplate>
</telerik:GridTemplateColumn>

I have also noticed that you call the column "Start_Time" and "Shift_Start_Time", make sure it is addressed correctly.

Regards,
Vasil
Telerik

[Archana]

Thanks Vasil, This works Perfectly!!

I'm facing one more issue, could you please help me out!!!

1) I want to insert created datetime and last updated datetime in entity database table when ever I insert or update a record??

but this columns I don't want to be visible in radgrid. It should be done in code behind automatically when I insert a new record/ update a record.

2) Dropdown with manual binding is not allowing me to insert/update, attached my code snippets below,

<telerik:GridTemplateColumn UniqueName="CheckBoxCol1"  HeaderText="Reason_Type" >
              <ItemTemplate>
              <%# DataBinder.Eval(Container.DataItem, "Reason_Type")%>
              </ItemTemplate>
             <EditItemTemplate>
               <telerik:RadComboBox ID="RadReasonType" Runat="server">
                   <Items>
                        <telerik:RadComboBoxItem Value="Downtime" Text="Downtime" />
                        <telerik:RadComboBoxItem Value="Rework" Text="Rework" />
                        <telerik:RadComboBoxItem Value="Rejection" Text="Rejection" />
                   </Items>
              </telerik:RadComboBox>
             </EditItemTemplate>
         </telerik:GridTemplateColumn>

 Let me know,where I'm wrong and what to change further...

 

Thanks in advance,

Archie

 

[Vasil]

Hello Archana,

You may want to check the manual data operations it actually covers both your questions.
http://demos.telerik.com/aspnet-ajax/grid/examples/data-editing/manual-crud-operations/defaultcs.aspx

Basically you handle the update command, and depending on the DataSource you modify it further by adding additional parameters to the queries.

Regards,
Vasil
Telerik