DataGrid Sort Expression for a date in format 'dd mmm yy'

[yer darn tootin]

Does anyone know the sort expression for a column that's data has been
returned in the format, eg '07 Jul 05'??

The sort expression {..:"dd mmm yy"} doesn't work ( if the column was
returned as '07-Mar-05' the expression {..:dd-MMM-yy} works OK

Second question, does anyone know hwo to return a date from SQL server
in the format '07-Mar-05', as this would be a workaround. At the moment
I'm using CONVERT(varchar(12),columnname,6 ) to return in format '07
Jul 05', but if I can't format that in the datagrid I suppose I could
return it in another way.

Whatever I do I need to see the MMM on the page, rather than a number
for the month.

Cheers all,
Bob

 

[Ken Cox [Microsoft MVP]]

Hi Bob,

The second part is easy using the DataFormatString attribute:


<asp:datagrid id="DataGrid1" runat="server"
AutoGenerateColumns="False">
<columns>
<asp:boundcolumn DataField="final_appvl_dt"
SortExpression="final_appvl_dt" DataFormatString="{0:dd-MMM-yy}"
ReadOnly="True" HeaderText="Final Apprvl Date"
ItemStyle-Wrap="false"></asp:boundcolumn>

</columns>
</asp:datagrid>

Private Sub Page_Load _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
DataGrid1.DataSource = CreateDataSource()
DataGrid1.DataBind()
End If
End Sub
Function CreateDataSource() As DataTable
Dim dt As New DataTable
Dim dr As DataRow
dt.Columns.Add(New DataColumn _
("IntegerValue", GetType(Int32)))
dt.Columns.Add(New DataColumn _
("StringValue", GetType(String)))
dt.Columns.Add(New DataColumn _
("CurrencyValue", GetType(Double)))
dt.Columns.Add(New DataColumn _
("Boolean", GetType(Boolean)))
dt.Columns.Add(New DataColumn _
("final_appvl_dt", GetType(DateTime)))
Dim i As Integer
For i = 0 To 4
dr = dt.NewRow()
dr(0) = i
dr(1) = "Item " + i.ToString()
dr(2) = 1.23 * (i + 1)
dr(3) = (i = 4)
dr(4) = Now.AddDays(i)
dt.Rows.Add(dr)
Next i
Return dt
End Function 'CreateDataSource

 

[Elton Wang]

Hi Bob,

You can convert "07 Jul 05" to "07-Jul-05" by using
string.Replace() method:

string strDate = "07 Jul 05";
strDate = strDate.Replace(" ", "-");

HTH

Elton Wang
(e-mail address removed)

 

[yer darn tootin]

Thanks for the replies, Ken and Elton. Always good to pick up some
stuff to investigate, novice that I am with this stuff.

Unfortunately, I think I may not have emphasised the exact nature of
the problem I was trying to solve.

I have a date column displayed in a datagrid which literally is in the
format 'dd mmm yy, eg it might be listing '07 Jan 05' ot '03 Jan 05'.
This is being returned from a SQL database sproc where I've used the
convert function on the date column, ie
convert(varchar(12),datecreated,6), which takes a date stored in the
format 2004-11-23 16:18:00 and changes it to '23 Nov 04' before
returning it back to the caller.


Behind the scenes, in the ASPX page's datagrid, I want to be able to
sort on this column, but as the date data on the form is in the format
'dd mmm yy' the Data formatting expression of {0:dd-MMM-yyyy} in the
datagrid for this column wouldn't sort it how I wanted, it was just
sorting by dd, numerically. I tried changing the expression to {0:dd
mmm yyyy} but it didn't seem to make a difference.

So I've taken the workaround option and am returning the date to the
form as '07-Jan-2005', rather than '07 Jan 05', so the sort expression
{0:dd-MMM-yyyy} works on that OK.

Hope this makes more sense now, if anyone has any suggestions.

Thanks again for the notes,
Bob

 

[Elton Wang]

In your case, it's better to directly sort Date rather
than string. So my suggestion is that don't do any convert
to date string in SP, hence it's easy to sort date type
data. Then when showing those date type data in datagrid
format them to specific string type, using
DataFormatString as Ken's post.

Elton Wang