Wrong date format C#, Oracle

Question

I'm trying to get my date in the correct format(dd/mm/yyyy). At the moment its in this format: MM-DD-YYYY HH24:MI:SS When I change it to dd/mm/yyyy, it works in the database(Oracle). As soon as I run it in my app I get exception: IndexOutOfRange at :

this.InfoList9.Add(dr["start_rcv_datetime"].ToString());

Please see my code below.

public List<String> InfoList = new List<String>();

private void populatelblDate()
{
    conn.Open();
    string query;
    query = "select to_char(dg.start_rcv_datetime,'dd/mm/yyyy') from dc_pallet dp, dc_pallet_stock dps , dc_grv dg , sku s ,prod_size ps,colour c ,purch_order_carton_sku pocs , dc_crane_instruc dci where dps.pallet_id_no = '" + palletId.ToString() + "' and dp.pallet_id_no = dps.pallet_id_no and dg.dc_grv_id_no = dps.dc_grv_id_no and dg.order_no = dps.order_no and dg.company_id_no = dps.company_id_no and s.company_id_no = dps.company_id_no and s.company_id_no = dg.company_id_no and dps.company_id_no = c.company_id_no and dps.company_id_no = ps.company_id_no and s.prod_size_id_no = ps.prod_size_id_no and s.colour_id_no = c.colour_id_no and dps.company_id_no = ps.company_id_no and pocs.order_no = dps.order_no and pocs.carton_code = dps.carton_code and pocs.company_id_no = dps.company_id_no and pocs.sku_id_no = s.sku_id_no and dci.pallet_id_no(+) = dp.pallet_id_no";

    OracleCommand cmd = new OracleCommand(query, conn);
    OracleDataReader dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        this.InfoList.Add(dr["start_rcv_datetime"].ToString());
    }
    dr.Close();
    conn.Close();
}

private void frmInfo_Load(object sender, EventArgs e)
{ 
    populatelblDate();
    lbl1.Text = this.InfoList[0];
}

Then I have a prev and next button as well...

Off-topic: Are you sure that you need all these joins in your SQL query? — stakx - no longer contributing
– stakx - no longer contributing, Commented Feb 11, 2013 at 8:34

Jon Skeet · Accepted Answer · 2013-02-11 08:30:08Z

6

Your IndexOutOfRange exception suggests that the immediate problem is that the result set doesn't contain a column of start_rcv_datetime - presumably because of the to_char conversion.

Don't deal with strings at the database side at all. Fetch the value as a DateTime, and then format it at the client in whatever you want to.

Use dr.GetDateTime to fetch the value, having removed the to_char part from your query:

query = "select dg.start_rcv_datetime from ...";

using (OracleCommand cmd = new OracleCommand(query, conn))
{
    using (OracleDataReader dr = cmd.ExecuteReader())
    {
        int dateColumn = dr.GetOrdinal("start_rcv_datetime");
        while (dr.Read())
        {
            DateTime date = dr.GetDateTime(0);
            // Or whatever - consider cultural implications
            string text = date.ToString("dd/MM/yyyy");
            InfoList.Add(text);
        }
    }
}

(Note the using statements - you should always make sure you clean up your database-related resources.)

answered Feb 11, 2013 at 8:30

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vladimir.RL Over a year ago

Just to add my case when transferring data from Oracle table to Ms Sql. In source table (Oracle) there is a field of DATE type. During transfer I used ExecuteReader and value of this field is written as string to destination table (Ms Sql). However, since year in question was 2032, later down the pipeline it was translated as 1932. Hence, for such cases it is mandatory to use this approach (get field type and, if date, format accordingly).

Jon Skeet Over a year ago

@Vladimir.RL: A better solution would be to avoid the "field written as string to destination table" part. If you don't have any formatting and parsing, it's hard for that sort of thing to happen...

Bob Vale · Accepted Answer · 2013-02-11 08:42:38Z

Your first column doesn't have a name andso you cannot retrieve it with "start_rcv_datetime"

The immediate solution would be to change the sql to read

query = "select to_char(dg.start_rcv_datetime,'dd/mm/yyyy') as start_rcv_datetime from dc_pallet dp, dc_pallet_stock dps , dc_grv dg , sku s ,prod_size ps,colour c ,purch_order_carton_sku pocs , dc_crane_instruc dci where dps.pallet_id_no = '" + palletId.ToString() + "' and dp.pallet_id_no = dps.pallet_id_no and dg.dc_grv_id_no = dps.dc_grv_id_no and dg.order_no = dps.order_no and dg.company_id_no = dps.company_id_no and s.company_id_no = dps.company_id_no and s.company_id_no = dg.company_id_no and dps.company_id_no = c.company_id_no and dps.company_id_no = ps.company_id_no and s.prod_size_id_no = ps.prod_size_id_no and s.colour_id_no = c.colour_id_no and dps.company_id_no = ps.company_id_no and pocs.order_no = dps.order_no and pocs.carton_code = dps.carton_code and pocs.company_id_no = dps.company_id_no and pocs.sku_id_no = s.sku_id_no and dci.pallet_id_no(+) = dp.pallet_id_no";

However you could simply return the date as a datetime from the database and then use string.Format on the result

eg.

this.InfoList.Add(string.Format("{0:dd/MM/yyyy}", dr["start_rcv_datetime"]));

Collectives™ on Stack Overflow

Wrong date format C#, Oracle

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related