How to format Time in GridDateTimeColumn?

16 posts, 0 answers
  1. Oleg
    Oleg avatar
    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
  2. Shinu
    Shinu avatar
    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
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Oleg
    Oleg avatar
    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
  5. Shinu
    Shinu avatar
    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
  6. Oleg
    Oleg avatar
    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
  7. Vasil
    Admin
    Vasil avatar
    1547 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.
  8. Oleg
    Oleg avatar
    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
  9. Peter
    Peter avatar
    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}""
  10. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 08 Jun 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.
  11. Archana
    Archana avatar
    5 posts
    Member since:
    May 2016

    Posted 09 Jun 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'.'.
  12. Archana
    Archana avatar
    5 posts
    Member since:
    May 2016

    Posted 09 Jun 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.

  13. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 09 Jun 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.
  14. Archana
    Archana avatar
    5 posts
    Member since:
    May 2016

    Posted 09 Jun 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

     

  15. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 09 Jun 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.
  16. Archana
    Archana avatar
    5 posts
    Member since:
    May 2016

    Posted 13 Jun 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

     

  17. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 16 Jun 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.
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017