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

Experts,

URGENT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

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.

Thanx.
sydneyramAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
Oracle Dates are a date and time together.

There is no such thing as  a "date only" type in Oracle.
0
sdstuberCommented:
you should be able to set the format of your date column in the grid.
0
sydneyramAuthor Commented:
sdstuber,

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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

OMC2000Commented:
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")  
0
sdstuberCommented:
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
0
sydneyramAuthor Commented:
OMC2000,

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 :).

Thanks.
0
mac-willCommented:
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

0
OMC2000Commented:
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"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>GridView Example</title>
</head>
<body>
<script runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
 OracleConnection connection = new OracleConnection("connection string");
    connection.Open();
    string selectcommand="Select admitdate, fname, dateofbirth, lname from Emp";
    OracleCommand command = new OracleCommand(selectcommand,connection);
    OracleDataAdapter adapter = new OracleDataAdapter(command);
 
    DataSet ds = new DataSet();
    adapter.Fill(ds);
    connection.Close();
    GridView1.DataSource = ds;
    GridView1.DataBind();
}
 
	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);
                }
            }
		}
	}
</script>
    <form id="form1" runat="server" >
      <asp:gridview id="GridView1" 
        autogeneratecolumns="true"
        emptydatatext="No data available." 
        allowpaging="true" 
        OnRowDataBound="GridView1_RowDataBound"
        runat="server">                
      </asp:gridview>
    </form>
  </body>
</html>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sydneyramAuthor Commented:
thanx experts. I really appreciate your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.