How do I format a date field in a dynamic datagrid with sorting?

[mdProgrammer]

I know how to format a date field, which appears as "12/4/2008 12:00 AM" in my datagrid before formatting. However, when it's formatted, a date like "1/2/2008" will always come before "2/2/2005" since it's now treating it like a string. How do I format this so that it treats it as a date?

Here's my setup... (I'm using ASP.NET 1.1)

I have a blank datagrid which gets data from the code behind. The exact column that will be a date field, if at all, is unknown, so I can't change the datagrid's datatype in the HTML view since it won't be there.

Here's an example table -

Field 1.....Field 2.....Field 3.....Date Field
data........data........data........1/1/2008
data........data........data........2/2/2007
data........data........data........1/15/2008
data........data........data........5/18/2008
data........data........data........12/3/2007

Obviously, I want the date field to be sorted when the user clicks on the column title to sort it. Since this is a search grid that I'm using, the date field won't always show, thus why I can't make this a bound datagrid.

Currently, the datagrid will show the date field as "1/1/2008 12:00AM", "2/2/2007 12:00AM", etc. This sorts perfectly until I make it a shortdatestring. I tried changing the column's datatype but it won't work since it has data.

 

[jbenson001]

You will have to sort the datasource first, before you bind it to the grid. If you have a datatable, then you can just create a new column of datetime type and copy the value from your data column into that column, then sort on the new column.

 

[jbenson001]

Or the best idea to would to do the sorting in the SQL.

 

[adamroof]

If you do have access to the SQL, i agree that would be best

try this out replacing your table information as needed.

SELECT TOP 15 
	CONVERT(char(10),auditDate,2) as 'SortableYearMonthDay',
	CONVERT(char(10),auditDate,101) as 'DisplayableDate',
	auditDate as 'Unformatted'
FROM tblAuditActions
	ORDER BY CONVERT(char(10),auditDate,2)

the CONVERT(char(10),auditDate,2) allows your datagrid to show it "properly sorted" when you code the sort command, but its not usually "readable" by those that are used to the mm/dd/yy format, so i usually just order it by the 2, and display it by the 101.


Ive been playing with the .net 2.0 introduced GridView and its built in sorting and paging, and all the work arounds and sql converting are handled very well. If you can move to at least 2.0 if not 3.5, now a good time to save you alot of work.