[2008] Details DataFormatString

[nmadd]

I know Google lights up like a Christmas tree when you search for something regarding date format in ASP.NET but I'm still having a problem with a BoundField and formatting its date.

ExpiryDate is varchar2(4000) in dd/mm/yyyy format.
SQL (Oracle 9i):

select
  ag.AGREEMENTS_ABBR LicensorNumber,
  (select r1.FIELD_VALUE
  from V_REPCUSTOMFIELDDATA r1
  where
    r1.INTERNAL_DESCRIPTIONS_CODE = 21
    and r1.ASSOCIATED_WITH_CODE = ag.AGREEMENTS_ABBR) ContractName,
  ag.AGREEMENTS_NAME ProjectName,
  ag.AGREEMENTS_AVAILDATE AvailableDate,
  to_date(ag.AGREEMENTS_EXPLEXPIRES, 'dd/mm/yyyy') ExpiryDate,
  (select to_date(r2.FIELD_VALUE, 'dd-mon-yyyy')
  from V_REPCUSTOMFIELDDATA r2
  where
    r2.INTERNAL_DESCRIPTIONS_CODE = 35
    and r2.ASSOCIATED_WITH_CODE = ag.AGREEMENTS_ABBR) SellOffDate,
  ag.FREQ_DISPLAY ReportFrequency 
from V_AGREEMENT ag
where ag.AGREEMENTS_ABBR = :licensorNum

DetailsView:

        <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False">
            <Fields>
                <asp:BoundField DataField="LicensorNumber" HeaderText="Licensor Number" />
                <asp:BoundField DataField="ProjectName" HeaderText="Project Name" />
                <asp:BoundField DataField="AvailableDate" 
                    DataFormatString="{0:MM/dd/yyyy}" HeaderText="Start Date" 
                    HtmlEncode="False" />
                <asp:BoundField DataField="ExpiryDate" 
                    DataFormatString="{0:MM/dd/yyyy}" HeaderText="Expiry Date" 
                    HtmlEncode="False" />
                <asp:BoundField DataField="SellOffDate" HeaderText="Sell Off Date" 
                    DataFormatString="{0:MM/dd/yyyy}" 
                    HtmlEncode="False" />
                <asp:BoundField DataField="ReportFrequency" HeaderText="Reporting Frequency" />
            </Fields>
        </asp:DetailsView>

SellOffDate (also a varchar2(4000)) appears formatted correctly in my DetailsView. ExpiryDate appears formatted as "12/31/99 12:00:00AM" in my Details View.

What simple thing am I missing this time?

EDIT: Please note that I cannot change the Data Types in the database.
EDIT2: Nuts - meant to say DetailsView in the title, of course.

[Tom Sawyer]

Month is upper case MM, not lower case mm (that's minutes or something).

Change your expiry date format to dd/MM/yyyy

[nmadd]

Month is upper case MM, not lower case mm (that's minutes or something).

Change your expiry date format to dd/MM/yyyy

Thanks Tom. I gave it a shot but that doesn't seem to be the problem. As far as I know, Oracle doesn't care if it's mm or MM. The DataFormatStrings in my DetailsView do use the correct MM though.

[Tom Sawyer]

It may be something to do with the to_date function then. Is the Expiry Date null or not a valid date? Since you're forcing it into a format, null and invalid values may be set to a default value and that may be it.

Try running the query in Query Analyzer (or whatever the Oracle equivalent is called) with the to_date removed and see what's in the field. If it's null or an invalid date, you may need to add a check for that before doing any formatting.

[mendhak]

Oracle doesn't care if it's mm or MM as it's not relevant to string formatting. The string formatting occurs after the data comes back to 'the code', where it is then being formatted. So it's the .NET code that cares whether it's mm or MM.

Therefore, why not return the date as-is from Oracle and format in the code?

[nmadd]

Therefore, why not return the date as-is from Oracle and format in the code?

Sounds easy enough. My problem is, I'm not entirely sure where to do this in my code.
I get my DataTable as such:

Dim ldBLL As New LicensorDetailBLL()
Dim dt1 As Data.DataTable = ldBLL.GetLicensorDetail(licensorNum)
Me.DetailsView1.DataSource = dt1
Me.DetailsView1.DataBind()

I'm unsure where I should convert my ExpiryDate field to a Date and format it appropriately. Shall I create a new DataColumn and copy the old ExpiryDate column over to it and convert to DateTime? Can I create a new DataColumn with an Expression that converts to DateTime?

Thanks for a push in the right direction.

[mendhak]

It does have a databound event in which you could manipulate the values.