Post reply

Removing Time from Date when adding to Gridview

  • October 31, 2017 at 9:11 am

    #341004

    I am trying to run a SELECT statement and post the results to a Gridview in C#.  Everything works fine except I can't seem to get rid of time on my date column in the Gridview.

    My column in SQL is set as a date.  When I run

    SELECT Start_Date FROM Table

    I get 2017-10-31

    I have tried to CAST and CONVERT the column in my stored procedure, but then I always get the below error when I run my application.

    "A field or property with the name 'Start_Date was not found on the selected data source."

    I do have a column named Start_Date.  The bound field in my Gridview is also set to Start_Date for the data field.

    Here is my query

    SELECT CAST(Start_Date as date)
    FROM Table
    WHERE Start_Date > GETDATE()

    If I remove the CAST, the code runs but I get time with the Date.  Anyone know something that I could do to get rid of the time in the Gridview?

  • October 31, 2017 at 9:15 am

    #1965994

    You haven't named your column. For example:
    SELECT CAST(Start_Date AS date) AS Start_Date
    FROM Table
    WHERE Start_Date > GETDATE();

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 31, 2017 at 9:19 am

    #1965996

    Thank you for the reply Thom.  I can't believe I missed that.  Well the error is gone but I still get time with date.

    Do I need to change the WHERE clause too?

  • October 31, 2017 at 9:23 am

    #1965998

    Matt.Altman - Tuesday, October 31, 2017 9:19 AM

    Thank you for the reply Thom.  I can't believe I missed that.  Well the error is gone but I still get time with date.

    Do I need to change the WHERE clause too?

    What do you mean by you "still get time with date"? A column of the datatype date does not contain a time value, thus, SQL server will not be returning one. Could it be that your front end application is putting the time in there due to the display format?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 31, 2017 at 9:30 am

    #1965999

    Possible.  But I can't figure out where that would be coming from.  This is all of my code to bind my grid.

       DataTable dt = new DataTable();
        SqlConnection con = new SqlConnection(str);
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.CommandType = commandType;

        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        sda.Fill(dt);
        gvPending.DataSource = dt;
        gvPending.DataBind();

  • October 31, 2017 at 9:39 am

    #1966002

    My knowledge of C# is pretty low (I can read it, but not write it), however, maybe try some cell formatting. A Google brought me to this conclusion, however, this is untested and I can't troubleshoot this (as I don't write C#):
    gvPending.Columns["Start_Date"].DefaultCellStyle.Format = "yyyy-MM-dd";

    Edit: This would go after your databind (I think).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 31, 2017 at 9:43 am

    #1966003

    Yeah I was looking at the same thing.  Didn't realize that the Gridview was getting the default C# format for Date which is DateTime.

    Thank you for the help.

  • November 1, 2017 at 10:19 am

    #1966223

    From what I remember (it's been a while), C# treats all SQL date datatypes as its own DateTime type and you have to specify the display format in the data grid at the cell or column level.

  • November 1, 2017 at 10:24 am

    #1966225

    Thanks Chris.  You are exactly right.  I didn't realize .NET would convert my date to a datetime.  Here is what I added to my ASP for that field in the Grid.

    dataformatstring="{0:MM/dd/yyyy}"

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply