Display Date on a GridView


I have a gridview that display records from a store procedure. One of the fields is Date.
I need to display the date as: mm/dd/yyyy right now it displays like this:
1/22/2009 12:00:00 AM
The date is coming from the stored procedure. How can I select the date as mm/dd/yyyy?
I have this:

e.EvaluationVisitID as [EvaluationVisitID], e.DateEvaluation as [DateEvaluation]
from tbl_EvaluationVisit e
where e.participantID=@participantID
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
select CONVERT( varchar(10),EvaluationVisitID ,101)
I would URGE you to leave your formatting to the application, and not do it in the select.  Doing it in your select statement will change it from a DATETIME to a VARCHAR.

If you must...

Select e.EvaluationVisitID as [EvaluationVisitID], convert(varchar(10), e.DateEvaluation,101) as [DateEvaluation]
from tbl_EvaluationVisit e
where e.participantID=@participantID

Open in new window

RomacaliAuthor Commented:
some of the dates got the year as 1900
10      01/01/1900                          
11      05/05/2009                          
12      06/27/2009                                 
16      01/01/1900                          
17      01/01/1900                          
18      01/01/1900                           
19      01/01/1900                          
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Aneesh RetnakaranDatabase AdministratorCommented:
those dates, u enter as '' , so sql represent them as the default date.
you can either update them as NULL  or filter them in you select query adding a where condition

where DateEvaluation  > '1901-01-01'
I take it your gridview is an ASP.NET gridview control.

If so, you need to set the DataFormatString property of the column in the gridview to {0:"mm/dd/yyyy"}.  If the gridview is databound and you are not explicitly defining boundfields in your markup you need to set this in code.

Take a look at this for more:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RomacaliAuthor Commented:
I got this error:
 Parser Error
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: Literal content ('<asp:BoundField  DataField="DateEvaluation" HeaderText="Date of Evaluation" DataFormatString="{0:"mm/dd/yyyy"} />') is not allowed within a 'System.Web.UI.WebControls.DataControlFieldCollection'.
RomacaliAuthor Commented:

I have to disagree with your allocation of points here.  

aneeshattingal provided you with what you requested.  "How can I select the date as mm/dd/yyyy?"
I provided you with the same statement, wrapped inside of your select, after explaining to you the better way to do it.
dublingills provided the C# solution that I proposed.  Which is correct.

But I think that a 3-way split is in order because we all three helped you.
You need to remove the quotation marks from the DataFormatString:

<asp:BoundField  DataField="DateEvaluation" HeaderText="Date of Evaluation" DataFormatString="{0:mm/dd/yyyy} />

My fault, should have made it clearer...


I think the issue here is one of interpretation; on the one hand Romacali's question clearly states:

'I need to display the date as: mm/dd/yyyy'

Which, to me defines the requirement.  However another part of the question is:

'How can I select the date as mm/dd/yyyy?'

Which has a different solution.

The bottom line here is that the required solution is simply a change of display format.  A change of data type in the datasource potentally introduces other issues but I'm not going to discuss the pros and cons of each approach.

I do this to help others and the time I get to spend on it is limited so I'm not big on the points thing.  If you feel your solution has been unfairly ignored please refer this to a moderator as I can't, and would happily reallocate points.
I couldn't agree more dublingills that you provided the proper solution.  That's what I was referring them to in my post.  I do feel it is important though to acknowledge the other two experts, myself being one of them.  But not enough to the point where I want to get a MOD involved.  I don't typically do the "request attention" myself.  Rather, I just ask that the asker review my comments and make their own decision.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.