locked
FORMAT DATE IN GRIDVIEW RRS feed

  • Question

  • User1717218719 posted

     I am looking to change the date format in EffDte gridview column to dd mmm yy format. currently it is just a string. I have tried doing it in the select sql statement. I am displaying it in a gridview. any help with this code would be great.

     With comComm
    
                    .Connection = conConn
                    .CommandType = CommandType.Text
                    '.CommandText = "SELECT * FROM X2_tblMaster WHERE (TaxCde = @TaxCde)"
                    .CommandText = "Select X,Y,Z Format([EffDte], 'dd mmm yyyy') as EffDteFROM tbl"
    
                   
                End With
    
                adpAdap = New SqlDataAdapter(comComm)
                adpAdap.Fill(DataS, "tbl")

    GridView1.DataSource = DataS.Tables("tbl")

    GridView1.DataBind()

    Wednesday, May 15, 2019 9:05 AM

Answers

  • User753101303 posted

    Hi,

    "It is just a string" is it is not the string you expect or you prefer to read an actual date/time than a string from SQL Server. If you keep your current approach SELECT FORMAT(GETDATE(),'dd MMM yyyy','en-gb') shows "15 May 2019" (might be best to force the language else you dépends on the connection config).

    You are using auto generated columns ? My preference is to handle that at the very last moment at the UI level so that it can be easily changed (and possibly support multiple languages). For example something based on https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.autogeneratedfield?view=netframework-4.8

    Not directly related but if using 4.5 you could consider to switch to https://weblogs.asp.net/dwahlin/asp-net-4-5-web-forms-features-model-binding over time...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 15, 2019 10:47 AM
  • User288213138 posted

    Hi E.RU,
     
    You can convert a string to a date type via DateTime.TryParse().


    And you need to specify expected format.


    The right approach is this:https://docs.microsoft.com/en-us/dotnet/api/system.datetime.tryparse?redirectedfrom=MSDN&view=netframework-4.8#System_DateTime_TryParse_System_String_System_DateTime__

    The code:

    public string str = ConfigurationManager.ConnectionStrings["Test1"].ConnectionString;
            string query = "select * from Test";
            protected void Page_Load(object sender, EventArgs e)
            {
                using(SqlConnection con=new SqlConnection(str))
                {
                    
                    using(SqlCommand cmd=new SqlCommand())
                    {
                        con.Open();
                        cmd.Connection = con;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = query;
    
    
                        SqlDataReader sdr = cmd.ExecuteReader();
                        List<string> d = new List<string>();
                        while (sdr.Read())
                        {
                            string s1 = Convert.ToString(sdr["Date"] );
                           
                            DateTime value;
                            DateTime.TryParse(s1, out value);                                               
                            d.Add(value.ToString("dd MMM yyyy"));
                        }
    
                        GridView1.DataSource = d;
                        GridView1.DataBind();
                 
                    }
                }
            }

    The result:

    Best Regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 16, 2019 3:34 AM

All replies

  • User753101303 posted

    Hi,

    "It is just a string" is it is not the string you expect or you prefer to read an actual date/time than a string from SQL Server. If you keep your current approach SELECT FORMAT(GETDATE(),'dd MMM yyyy','en-gb') shows "15 May 2019" (might be best to force the language else you dépends on the connection config).

    You are using auto generated columns ? My preference is to handle that at the very last moment at the UI level so that it can be easily changed (and possibly support multiple languages). For example something based on https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.autogeneratedfield?view=netframework-4.8

    Not directly related but if using 4.5 you could consider to switch to https://weblogs.asp.net/dwahlin/asp-net-4-5-web-forms-features-model-binding over time...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 15, 2019 10:47 AM
  • User288213138 posted

    Hi E.RU,
     
    You can convert a string to a date type via DateTime.TryParse().


    And you need to specify expected format.


    The right approach is this:https://docs.microsoft.com/en-us/dotnet/api/system.datetime.tryparse?redirectedfrom=MSDN&view=netframework-4.8#System_DateTime_TryParse_System_String_System_DateTime__

    The code:

    public string str = ConfigurationManager.ConnectionStrings["Test1"].ConnectionString;
            string query = "select * from Test";
            protected void Page_Load(object sender, EventArgs e)
            {
                using(SqlConnection con=new SqlConnection(str))
                {
                    
                    using(SqlCommand cmd=new SqlCommand())
                    {
                        con.Open();
                        cmd.Connection = con;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = query;
    
    
                        SqlDataReader sdr = cmd.ExecuteReader();
                        List<string> d = new List<string>();
                        while (sdr.Read())
                        {
                            string s1 = Convert.ToString(sdr["Date"] );
                           
                            DateTime value;
                            DateTime.TryParse(s1, out value);                                               
                            d.Add(value.ToString("dd MMM yyyy"));
                        }
    
                        GridView1.DataSource = d;
                        GridView1.DataBind();
                 
                    }
                }
            }

    The result:

    Best Regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 16, 2019 3:34 AM