Telerik Forums / UI for ASP.NET AJAX Forum / Grid /

How to format Time in GridDateTimeColumn?

Telerik UI for ASP.NET AJAX
Resources Buy Try

16 posts, 0 answers

Oleg

12 posts

Member since:
Jul 2012

Posted 15 Jan 2014 Link to this post

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

17764 posts

Member since:
Mar 2007

Posted 15 Jan 2014 Link to this post

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

12 posts

Member since:
Jul 2012

Posted 16 Jan 2014 Link to this post

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

17764 posts

Member since:
Mar 2007

Posted 17 Jan 2014 Link to this post

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

12 posts

Member since:
Jul 2012

Posted 17 Jan 2014 Link to this post

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
Admin

1642 posts

Posted 21 Jan 2014 Link to this post

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

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
Oleg

12 posts

Member since:
Jul 2012

Posted 30 Jan 2014 in reply to Vasil Link to this post

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

Regards,
Oleg
Peter

98 posts

Member since:
Mar 2012

Posted 06 Jan 2015 in reply to Oleg Link to this post

This post is useful:

http://stackoverflow.com/questions/4703695/timespan-dataformatstring-in-gridview

"You can't use two colons, so use the short time format specifier.DataFormatString="Time is {0:t}"
t is the equivalent of HH:mm
T is the equivalent of HH:mm:ss"

"I believe you need to escape the second ':' if you are using C#. For example:
DataFormatString=@"Time is {0:H\:mm}""
Vasil
Admin

1642 posts

Posted 08 Jun 2016 Link to this post

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
Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
Archana

5 posts

Member since:
May 2016

Posted 09 Jun 2016 Link to this post

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'.'.
Archana

5 posts

Member since:
May 2016

Posted 09 Jun 2016 in reply to Archana Link to this post

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
Admin

1642 posts

Posted 09 Jun 2016 Link to this post

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
Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
Archana

5 posts

Member since:
May 2016

Posted 09 Jun 2016 in reply to Vasil Link to this post

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
Admin

1642 posts

Posted 09 Jun 2016 Link to this post

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
Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
Archana

5 posts

Member since:
May 2016

Posted 13 Jun 2016 in reply to Vasil Link to this post

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
Admin

1642 posts

Posted 16 Jun 2016 Link to this post

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
Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.

How to format Time in GridDateTimeColumn?

Oleg

Shinu

Oleg

Shinu

Oleg

Vasil

Oleg

Peter

Vasil

Archana

Archana

Vasil

Archana

Vasil

Archana

Vasil