Display Date on a GridView

Romacali
Romacali used Ask the Experts™
on
Hello,

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:
Select


e.EvaluationVisitID as [EvaluationVisitID], e.DateEvaluation as [DateEvaluation]
from tbl_EvaluationVisit e
where e.participantID=@participantID
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
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

Author

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                          
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

AneeshDatabase Consultant
Top Expert 2009

Commented:
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:
http://msdn.microsoft.com/en-us/library/aa479342.aspx

Author

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

Author

Commented:
Thanks!
Romacali:

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

Guys,

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial