DateTime Format to YYYY-mm-dd conversion c# asp.net

Question

I am having trouble in date conversion in asp.net c#. i have the following method in my GeneralMethods class

public DateTime mySqlDateFormate(string dateString, string format="g")
{
    dateString = isOk(dateString) ? dateString : DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;
    CultureInfo provider = CultureInfo.InvariantCulture;
    // Parse date-only value with invariant culture.
    try
    {
        result = DateTime.ParseExact(dateString, format, provider);
        //Console.WriteLine("{0} converts to {1}.", dateString, result.ToString());
    }
    catch (Exception exc)
    {
        catchError(exc);
    }
    return result;
}

I am sending parameters from page to this method as:

protected void Page_Load(object sender, EventArgs e)
{
    if(gm.isOk((string)Session["userid"])){
        var sqlAry = "SELECT sno,std_name,gender,nic,father_name,father_nic,guardian_name,guardian_nic,dob,domicile,addres,reg_date,last_institute FROM std_basic_info WHERE sno = " + (string)Session["std_sno"];
        DataTable data = gm.dataTable(sqlAry);
        foreach (DataRow row in data.Rows)
        {
            snoTbl = row["sno"].ToString();
            std_nameTbl = row["std_name"].ToString();
            genderTbl = row["gender"].ToString();
            nicTbl = row["nic"].ToString();
            father_nameTbl = row["father_name"].ToString();
            father_nicTbl = row["father_nic"].ToString();
            guardian_nameTbl = row["guardian_name"].ToString();
            guardian_nicTbl = row["guardian_nic"].ToString();
            dobTbl = row["dob"].ToString();
            domicileTbl = row["domicile"].ToString();
            addresTbl = row["addres"].ToString();
            reg_dateTbl = row["reg_date"].ToString();
            institutionLstAttendedTbl = row["last_institute"].ToString();
        }
            this.std_sno.Value = snoTbl;
            this.stdName.Text = std_nameTbl;
            if (genderTbl.Equals("1")) { this.sex.SelectedIndex = 0; } else { this.sex.SelectedIndex = 1; }
            this.stdNic.Text = nicTbl;
            this.fatherName.Text = father_nameTbl;
            this.fatherNic.Text = father_nicTbl;
            this.guardianName.Text = guardian_nameTbl;
            this.guardianNic.Text = guardian_nicTbl;
            this.dob.Text =  gm.mySqlDateFormate(dobTbl,"d").ToString();
            this.domicile.Text = domicileTbl;
            this.institutionLstAttended.Text = institutionLstAttendedTbl;
            this.address.Text = addresTbl;
        }
}

in mySql table value in dob columns is as:

1978-04-23

Everything goes fine, but when it fetch value from table it shows like this:

1/1/0001 12:00:00 AM

I am not getting the original value in my textfield?

Please anyone help me in this...

Thanks

Answer 1

Matt Johnson,** this fixed my problem and also i have followed your other tips, which are really very appreciated. Thanks a lot.

I just change this :

dateBirth = (DateTime)row["dob"];

And then used:

this.dob.Text = dateBirth.Year+"-"+dateBirth.Month+"-"+dateBirth.Day;

Answer 2

There are several antipatterns present in your code. Let's take a look at them one by one.

• Unnecessary String Conversion

  dobTbl = row["dob"].ToString();
  

  You should not be calling .ToString() on every single value coming from your database. That immediately throws away any type information that might be present, and forces you needlessly down the path of string formatting and parsing. Instead, you should be casting to the actual data type returned:

  dobTbl = (DateTime) row["dob"];
  

  The same applies to all other columns. Cast to (string), (int), or whatever the real data type is.

• Using DataTables

  DataTable data = gm.dataTable(sqlAry);
  

  When you use the DataTable class, you are loading all records from your query into memory. If you have a large dataset, that can be slow and will consume a lot of memory. You should instead look to using a DataReader which just works with your data one row at a time.

  I'm a bit confused though why you are working with multiple rows to begin with, as it appears you are just using the last row of the results in your textboxes. If you have more than one record being returned by your query, the data for rows 1 through n-1 are being discarded.

• Multiple calls to the system clock

  DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day
  

  Every time you call DateTime.Now, that is a new call to retrieve the time from the system clock. By calling it multiple times in the same procedure, you risk having invalid data if your code happens to be running over a day boundary and the timing is just right. Besides, it's unnecessary. Just call it once and put the results in a local variable:

  DateTime now = DateTime.Now;
  

  Then you can use now wherever you need it.

• Composing a DateTime from parts

  If you want the current date as a string, don't assemble it from parts. Just use:

  DateTime.Today.ToString("yyyy-MM-dd");
  

• Working against the framework

  Your entire mySqlDateFormate function could be replaced with DateTime.TryParseExact, and be a lot more reliable.

• String Concatenation of SQL

  ... " WHERE sno = " + (string)Session["std_sno"];
  

  This leaves your code vulnerable to SQL Injection attacks, and also prevent the SQL query engine from properly caching an execution plan for your query. You should be using parameratized queries instead. There is a ton of information about this on StackOverflow and the web in general, so I won't go on further about it here.

Answer 3

Change:

gm.mySqlDateFormate(dobTbl,"d").ToString();

To

gm.mySqlDateFormate(dobTbl,"yyyy-MM-dd").ToString();

Answer 4

I'm not sure what format you are passing to mySqlDateFormate method

result = DateTime.ParseExact(dateString, format, provider);

Make sure format is "YYYY-MM-dd"

note that i've used upper case MM and not mm

Answer 5

I'm assuming this piece is responsible for the DateTime to text box assignment gm.mySqlDateFormate(dobTbl,"d").ToString();, the fix is very simple;

  this.dob.Text =  gm.mySqlDateFormate(dobTbl,"d").ToString("yyyy-MM-dd");

Maybe that format specifier can replace the "d" in mySqlDateFormate. I don't really know what that method is there for. There is a ToString overload on DateTime which accepts a format specifier and is sufficient. If that's a custom method just stop using it or make it's implementation simply return input.ToString("yyyy-MM-dd");