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

Posted on 2008-11-07
Last Modified: 2013-12-19


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.

Question by:sydneyram
    LVL 73

    Expert Comment

    Oracle Dates are a date and time together.

    There is no such thing as  a "date only" type in Oracle.
    LVL 73

    Expert Comment

    you should be able to set the format of your date column in the grid.

    Author Comment


    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.
    LVL 15

    Expert Comment

    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")  
    LVL 73

    Expert Comment

    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

    Author Comment


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

    LVL 8

    Assisted Solution

    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

    LVL 15

    Accepted Solution

    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


    Author Closing Comment

    thanx experts. I really appreciate your help.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now