let’s say you retrieve a few dates from your database in this format:
2009-12-16 00:00:00.000
2010-01-20 00:00:00.000
2010-03-03 00:00:00.000
Now it is time to display them in a drop down list, before doing so you want to make sure that you have the right format. You would want to display something like 12/16/2009 in the drop down right?
You have two options:
1. Format the date in SQL first using something like :
select Distinct CONVERT(VARCHAR(10), MyDate 101) as MyDate from Table order by MyDate
Very soon you will find out that SQL does a bad poor job at ordering the list because the ordering happens after the conversion.
2. Use the Prerender event of the Dropdownlist
First the “DataTextFormatString” property does not work with date (stupid right?). The only way i have been able to get the right date is to use the “Prerender” Event of the Dropdown list and Loop through the items and change them on the Fly.
Protected Sub dd_date_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles dd_date.PreRender
Dim i As Integer
Dim MyDateTime As DateTime
For i = 1 To Me.dd_date.Items.Count - 1
MyDateTime = FormatDateTime(Me.dd_date.Items(i).Text, DateFormat.ShortDate)
Me.dd_date.Items(i).Text = MyDateTime.ToString("MM/dd/yyyy")
Me.dd_date.Items(i).Value = MyDateTime.ToString("MM/dd/yyyy")
Next
End Sub
Hopefully that will save some people some time