CODEWITHSUNDEEP
c#sqlasp.netsql-servergridview
Vasanth
Vasanth

Reputation: 128

Grid view to display date format in MM-DD-YYYY format

I have a gridview to display date on button click depending on the checkbox(s) selected. I have used string concatenation to fetch data to manage dynamic changes at the UI. Currently, it displays in this format - MM-DD-YYYY 12:00:00 AM, by default but I need it in MM-DD-YYYY format.

With BoundField, DataFormatString="{0:d}" HtmlEncode="False", I get the desired output but I can't build the dynamic SQL connection string. I have referred the tutorials and websites but most have them have provided solution with fixed SQL query or by using Gridview's "Choose Data Source" option. In my case, since its dynamic query, I can't Choose Data Source, I guess.

Is it possible to set a particular column's property of gridview to display the desired format.? Please suggest. Kindly share your thoughts with sample code (will be more helpful).

The code is as follows. The current output is attached.

Code:

DataTable GetData(int Count)
{
    DataTable dt = new DataTable();
    string query = "SELECT [NAME], [Date], Duration FROM dbo.OnLeaveDetails WHERE @User = Username AND ";
    string whereClause = "[Leave Type] = ";

    if(CheckBox1.Checked)
        query += whereClause + "@" + <TYPE1>;
    ....
    ....

    Command = new SqlCommand(query, Conn);

    try
    {
        Conn.Open();
        Command.Parameters.Add("User", SqlDbType.NVarChar).Value = Username;

        if (CheckBox1.Checked)
            Command.Parameters.Add(<TYPE>, SqlDbType.NVarChar).Value = LeaveType[0];
        ....
        ....

        SqlDataAdapter adpt = new SqlDataAdapter(Command);
        adpt.Fill(dt);
        Conn.Close();
        return dt;
    }
    catch ()
    {....}
}

protected void Submit_Click(object sender, EventArgs e)
{
    GridView1.DataSource = GetData(count);
    GridView1.DataBind();
}

Upvotes: 1

Views: 2267

Answers (1)

John
John

Reputation: 1852

instead of [Date] you should use MS SQL CONVERT function:

CONVERT(varchar(10), [Date], 101)

101 = mm/dd/yyyy

you can format your date in many ways, all of them are listed here

Upvotes: 1

Related Questions