Removing Time from Date when adding to Gridview

Question

Removing Time from Date when adding to Gridview

Matt.Altman

Say Hey Kid

Points: 705
More actions
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?

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

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

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

Matt.Altman Say Hey Kid Points: 705 More actions · Answer 2

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?

Thom A SSC Guru Points: 99130 More actions · Answer 3

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

Matt.Altman Say Hey Kid Points: 705 More actions · Answer 4

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();

Thom A SSC Guru Points: 99130 More actions · Answer 5

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

Matt.Altman Say Hey Kid Points: 705 More actions · Answer 6

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.

Chris Wooding SSCarpal Tunnel Points: 4556 More actions · Answer 7

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.

Matt.Altman Say Hey Kid Points: 705 More actions · Answer 8

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}"