Oracle's Date field is shown as DateTime in .NET GridView- Only need the Date part not the Time



I need your assistance in converting a Date field returned from an ORACLE database(PLZ dont give SQL SERVER Solutions :)  ) into a .NET Date field which is displayed in a Grid View which shows only the Date part and not the Time
(For Eg: the Value in GridView is  6/11/1986 12:00:00 AM       and I want it as just 6/11/1986)

I cannot create that column as a bound field as my DataSet returns variable columns everytime. So, there might or might not be a DateField in the DataSet too depending on the Query I run against the Database.

I TRIED USING  TO_CHAR(datecolumn, 'MM/DD/YY) function which converts it to CHAR TYPE and upon ORDER BY, that that particular filed is ordered as a STRING and NOT AS A DATE type.

An early response would be really appreciated and helpful.

Who is Participating?
OMC2000Connect With a Mentor Commented:
DataGridViewColumn is a memeber of Win Forms namespace, but we are talking about ASP.NET application.
Corrspondent class DataControlField does not contain information about ValueType.
Probably I miss something but it looks like the approach suggested by mac-will won't work

The following example works, but it is also it not the best solution becose it is based on date fields name

<%@ Page language="C#" %>
<%@import namespace="System.Data" %>
<%@import namespace="System.Data.OracleClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
<html xmlns="" >
  <head runat="server">
    <title>GridView Example</title>
<script runat="server">
private void Page_Load(object sender, System.EventArgs e)
 OracleConnection connection = new OracleConnection("connection string");
    string selectcommand="Select admitdate, fname, dateofbirth, lname from Emp";
    OracleCommand command = new OracleCommand(selectcommand,connection);
    OracleDataAdapter adapter = new OracleDataAdapter(command);
    DataSet ds = new DataSet();
    GridView1.DataSource = ds;
	protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
		if (e.Row.RowType == DataControlRowType.DataRow)
            for (int i = 0; i < e.Row.Cells.Count; i++)
                string fName = (string)((DataControlFieldCell)e.Row.Cells[i]).ContainingField.HeaderText;
                if (fName.ToUpper().IndexOf("DATE") >= 0)
                    e.Row.Cells[i].Text = e.Row.Cells[i].Text.Substring(0,10);
    <form id="form1" runat="server" >
      <asp:gridview id="GridView1" 
        emptydatatext="No data available." 

Open in new window

Oracle Dates are a date and time together.

There is no such thing as  a "date only" type in Oracle.
you should be able to set the format of your date column in the grid.
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

sydneyramAuthor Commented:

I can set the format of my date column in the grid if it is a BOUND FIELD. And in my case I CANNOT BIND to one particular column since my query might return 1 to N  variable columns every time depending on what columns the user desires to view.
For example:
CASE 1: Select fname, lname from Emp;
CASE 2: Select dateofbirth, fname, lname from Emp;
CASE 3: Select dateofbirth, admitdate from Emp;
CASE 4: Select admitdate, fname, dateofbirth, lname from Emp;

IN CASE 1,  there is no date field at all but in the others  there are date fileds. So, I cannot say which column will be in what order in the resultant Dataset that I bind to the my Grid View(it is auto generated to be more precise).

Hope I made my case more clear now.
1. you could convert Oracle date to string using 'YYYY/MM/DD' format, than ordaring should work properly

2. regardless your dynamic query having different sets of columns you somehow display them, I believe you could add code which can detect whither date field is returned and set format for it in the column definition.
For instance you could add processing of Bound event for your GridView and format value of Date column to string using  thisDate.ToString("d")  
yes, even if you have a dynamic columns, you still have the ability (maybe some additional coding) to find the column that is a date and set that column's format
sydneyramAuthor Commented:

I thought of the same solution you specified in your Point #1. But the requirement states that the format of the date MUST be in MM/DD/YYYY which is the standard format. So, thats where my hands are cut off :(.

I would surely love to take the second approach you suggested, I thought of solutions like that but it in vain. Do you have a code snippet which could do that? I would be grateful if you really can send it over :).

mac-willConnect With a Mentor Commented:
When you set the grid datasource loop through all the columns and for any column that is a date column set the format.

something like:
myDataGrid.DataSource = Database.Select....
foreach(DataGrivViewColumn col in myDataGrid.Columns)
if (col.ValueType == typeof(DateTime))
col.DefaultCellStyle.Format = "dd/MM/yyyy";

Open in new window

sydneyramAuthor Commented:
thanx experts. I really appreciate your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.