Select statement not working properly in SqlCommand

Question

I'm using the below code to create an sql data grid on a web page in an ASP.Net web application.

private void BindGrid()
    {
        string strConnString = "server= N-1077; Trusted_Connection=yes; database=Slaughter; connection timeout=30";
        using (SqlConnection con = new SqlConnection(strConnString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT WeekEndingDate = CONVERT(date, Week_Ending_Date, 103), Week_Number, North_Island, South_Island FROM Slaughter ORDER BY WeekEndingDate DESC"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                    }
                }
            }
        }
    }

I'm not sure why but when I call the select statement - "SELECT WeekEndingDate = CONVERT(date, Week_Ending_Date, 103), Week_Number, North_Island, South_Island FROM Slaughter ORDER BY WeekEndingDate DESC" - the WeekEndingDate still shows up on the web page with the datetime.

enter image description here

If I run the same command in Sql Server it does it correctly.

enter image description here

So what am I doing wrong here? Here is the html side of things just in case that is the problem.

<div style="width: 1250px; height: 300px; overflow: auto">
    <asp:GridView ID="GridView1" HeaderStyle-BackColor="#6699ff" HeaderStyle-ForeColor="Black" RowStyle-BackColor="#ccffff" AlternatingRowStyle-BackColor="White" 
        AlternatingRowStyle-ForeColor="#000" runat="server" AutoGenerateColumns ="false" AllowPaging="false" OnPageIndexChanging="OnPageIndexChanging" AllowSorting="True">
        <Columns>
            <asp:BoundField DataField ="WeekEndingDate" HeaderText="Week Ending Date" ItemStyle-Width="150px" />
            <asp:BoundField DataField ="Week_Number" HeaderText="Week Number" ItemStyle-Width="150px" />
            <asp:BoundField DataField ="North_Island" HeaderText="North Island" ItemStyle-Width="150px" />
            <asp:BoundField DataField ="South_Island" HeaderText="South Island" ItemStyle-Width="150px" />
        </Columns>
    </asp:GridView>
</div>

Remove 103 from convert that is not needed

Pரதீப்
– Pரதீப்

2015-01-20 01:31:08 +00:00
Commented Jan 20, 2015 at 1:31 — Pரதீப்
– Pரதீப், Commented Jan 20, 2015 at 1:31

Pankaj Kapare · Accepted Answer · 2015-01-20 01:36:48Z

3

This will format date:

<asp:BoundField DataField ="WeekEndingDate" HeaderText="Week Ending Date" ItemStyle-Width="150px" dataformatstring="{0:MM-dd-yyyy}"/>

edited Jan 20, 2015 at 1:36

answered Jan 20, 2015 at 1:30

Pankaj Kapare

7,8725 gold badges42 silver badges58 bronze badges

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

5 Comments

Danrex Over a year ago

So close but I'm getting the date format like this 00-09-2015 and all days are 00?

Mark Fitzpatrick Over a year ago

Because ASP.Net has a datetime datatype. That's means every date has a time. Since the query returns 0 time, that's why it's being displayed as 12:00:00 AM when it is being bound as you get the full date + time when it displays it. You have to format the data from there as Pankaj Kapare has illustrated with the dataformatstring

Pankaj Kapare Over a year ago

Corrected date format. It should work as expected now.

Danrex Over a year ago

As a matter of interest why the difference between mm and MM?

Pankaj Kapare Over a year ago

The small “m” and “mm” gives you the minute without and with a leading zero respectively. The capital “M” and “MM” gives you the month without and with a leading zero respectively.

JC Ford · Accepted Answer · 2015-01-20 01:39:56Z

1

CONVERT(date, Week_Ending_Date, 103) converts the value of Week_Ending_Date to a date datatype in the returned dataset. .NET receives that as a DateTime and the default string format of a DateTime includes the time value. That's why your web page has the time displayed. If you do not want the time displayed, convert the value to a string either in your SQL: CONVERT(nvarchar(10), Week_Ending_Date, 103) or in your ASP tag: dateformatstring="{0:d}" or dateformatstring="{0:dd-MM-yyyy}" if your current culture settings don't give you the format you want with the first one.

answered Jan 20, 2015 at 1:39

JC Ford

7,0963 gold badges27 silver badges36 bronze badges

Collectives™ on Stack Overflow

Select statement not working properly in SqlCommand

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related