Convert DateTime to month in a query

Question

I have this query to insert the data into a chart. The problem is that when the graph appears ... the date appears numerical and I would like it to appear before with the name of the month referring to that date and if possible the year also.

[HttpPost]
public JsonResult AjaxMethod()
{
    string query = "SELECT DataEntrada, COUNT(ID_Reserva) TotalReservas From Reserva Group by DataEntrada";
    var constr = ConfigurationManager.ConnectionStrings["Hotel"].ConnectionString;
    List < object > chartData = new List<object>();
    chartData.Add(new object[]
                        {
            "DataEntrada", "TotalReservas"
                        });
    using(SqlConnection con = new SqlConnection(constr))
    {
        using(SqlCommand cmd = new SqlCommand(query))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            using(SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read()) {
                    chartData.Add(new object[]
                        {
                            sdr["DataEntrada"], sdr["TotalReservas"]

                        });
                }
            }
            con.Close();
        }
    }
    return Json(chartData);
}

Final result

Could you provide some sample data from `Reserva` table and explain more about your expect result ? — D-Shih, Apr 09 '18 at 14:18

Matheus Cuba · Accepted Answer · 2018-04-09T14:33:13.307

That is because you are returnig the datetime field of the database in the Json format, try converting it first to string like this:

while (sdr.Read())
{
    chartData.Add(new object[]
    {
        ((DateTime?)sdr["DataEntrada"]).ToString("dd/MM/yyyy"), sdr["TotalReservas"]
    });
}

I presume by your name and database fields name that your are also brazilian, so your date format is (dd/MM/yyyy)

Or

If you really need it in the datetime format to your chart plugin, you can convert it in JavaScript as seen here, or even add a new field in your JSON to add the formated datetime in string aside your datetime json

Edit

To format date to show month name use ((DateTime?)sdr["DataEntrada"]).ToString("MMMM")

Convert DateTime to month in a query

1 Answers1