DateTime format in SQL

Question

I am having some problem when trying to format DateTime in Asp.net. I wanted the date to display as 18/1//2014 instead of 18/1/2014 12.00 AM. Here is the code:

DataTable dt = new DataTable();
        dt.Columns.Add("totalQuantity");
        dt.Columns.Add("deliveryDate");

        for (int count = 0; count < catSumList.Count; count++)
        {
            DataRow dr = dt.NewRow();
            dr["totalQuantity"] = catSumList[count].productQuantity;
            dr["deliveryDate"] = catSumList[count].deliveryDate;
            dt.Rows.Add(dr);
        }

        string[] deliveryDate = new string[dt.Rows.Count];
        decimal[] totalQuantity = new decimal[dt.Rows.Count];
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            totalQuantity[i] = Convert.ToInt32(dt.Rows[i][0]);
            deliveryDate[i] = dt.Rows[i][1].ToString("dd/M/yyyy", CultureInfo.InvariantCulture);
        }

lcCategory.Series.Add(new AjaxControlToolkit.LineChartSeries { Data = totalQuantity });
        lcCategory.CategoriesAxis = string.Join(",", deliveryDate);
        lcCategory.ChartTitle = string.Format(categoryName);
        lcCategory.Visible = true;

However, it gives me an error message at this line:

deliveryDate[i] = dt.Rows[i][1].ToString("dd/M/yyyy", CultureInfo.InvariantCulture);

The error message is No overload method ToString takes 2 arguments. I wonder is there any other way to format it? My data type for deliveryDate in database is DateTime. Thanks in advance.

score 2 · Accepted Answer · answered Jan 18 '14 at 13:15

2

Since you are working with DataTables which are weakly typed and not recommended to be used you will need to first cast to a DateTime before being able to apply any format:

deliveryDate[i] = ((DateTime)dt.Rows[i][1]).ToString("dd/M/yyyy", CultureInfo.InvariantCulture);

The Rows property returns an object which you need to cast.

answered Jan 18 '14 at 13:15

Darin Dimitrov

1,023,142
271
3,287
2,928

In this case in your database you do not have a datetime field for this property. It's already some nvarchar. What does `dt.Rows[i][1].GetType()` return in your debugger? – Darin Dimitrov Jan 18 '14 at 13:20
Oh ya it's Date data type. Sorry my bad – Jan 18 '14 at 13:21
What does `dt.Rows[i][1].GetType()` return in your debugger? – Darin Dimitrov Jan 18 '14 at 13:23
It returns me string[4]. But in my database the data type is Date. And in my data access layer, I put the data type for deliveryDate as DateTime – Jan 18 '14 at 13:32
Try using a `datetime` column in your database. – Darin Dimitrov Jan 18 '14 at 13:34
oh I've fixed it already. I changed the line inside for loop to dr["deliveryDate"] = catSumList[count].deliveryDate.ToString("dd/M/yyyy", CultureInfo.InvariantCulture); – Jan 18 '14 at 13:35

score 0 · Answer 2 · answered Jan 18 '14 at 13:18

0

Use:

    deliveryDate[i] = ((DateTime)dt.Rows[i[1]).
ToString("dd/M/yyyy",CultureInfo.InvariantCulture);

answered Jan 18 '14 at 13:18

DateTime format in SQL

2 Answers2