By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,776 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,776 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

"yer darn tootin" <bo*************@yahoo.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
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


Nov 19 '05 #2

P: n/a
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
el********@hotmail.com

-----Original Message-----
Does anyone know the sort expression for a column that's data has beenreturned in the format, eg '07 Jul 05'??

The sort expression {..:"dd mmm yy"} doesn't work ( if the column wasreturned as '07-Mar-05' the expression {..:dd-MMM-yy} works OK
Second question, does anyone know hwo to return a date from SQL serverin the format '07-Mar-05', as this would be a workaround. At the momentI'm using CONVERT(varchar(12),columnname,6 ) to return in format '07Jul 05', but if I can't format that in the datagrid I suppose I couldreturn it in another way.

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

Cheers all,
Bob

.

Nov 19 '05 #3

P: n/a

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

Nov 19 '05 #4

P: n/a
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

-----Original Message-----

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

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

I have a date column displayed in a datagrid which literally is in theformat '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 theconvert function on the date column, ie
convert(varchar(12),datecreated,6), which takes a date stored in theformat 2004-11-23 16:18:00 and changes it to '23 Nov 04' beforereturning it back to the caller.
Behind the scenes, in the ASPX page's datagrid, I want to be able tosort 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 thedatagrid for this column wouldn't sort it how I wanted, it was justsorting by dd, numerically. I tried changing the expression to {0:ddmmm yyyy} but it didn't seem to make a difference.

So I've taken the workaround option and am returning the date to theform 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

.

Nov 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.