[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1119
  • Last Modified:

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.
0
sydneyram
Asked:
sydneyram
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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
 
sydneyramAuthor Commented:
thanx experts. I really appreciate your help.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now