Date format Problem

skhan · September 2nd, 2009, 04:20 PM

Hello,
I am using a MSAccess database date entered into the database are Medium date format ie. dd-mmm-yy format. But when the data is pulled from the db and shown into a datagrid, it's like mm/dd/yyyy hh:mm:ss. But I want only the medium date that is dd-mm-yy format(18-Aug-09). Please help.
Thanks in advance.

kokoness · September 2nd, 2009, 08:02 PM

If I remember it correctly, you could do this:

this.dataGridView1.Columns["MyDate"].DefaultCellStyle.Format = "dd-MMM-yy";

conversion example

Code:

        static void Test1()
        {
            DateTime today = DateTime.Now;

            string formattedDate = today.ToString("dd-MMM-yy");

            Console.WriteLine(formattedDate);
        }

Old Pedant · September 2nd, 2009, 08:15 PM

You might also want to read this:
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=189

I wrote it, years ago, about ASP. But it's equally true of ASP.NET. *NO* format setting that you use in any database will carry over to the ASP/ASP.NET/JSP/PHP code. Read why in that FAQ.

skhan · September 3rd, 2009, 09:53 AM

Thank you for your reply, but how do I declare dataGridView1?.....I have a datagrid. but would it be like DataGridView dataGridView1= new DataGridView();

Everywhere they talk about this object but I can't find how to declare it? Please suggest.

skhan · September 3rd, 2009, 10:14 AM

Thanks Old Pedant. The link was helpful. I get to know a lot about how it actually works.

skhan · September 3rd, 2009, 10:58 AM

I am trying to format the date as follows:

Code:

foreach (DataRow r in ds.Tables[0].Rows)
 {
       string date1= r["First_Resurvey_Date"].ToString(); //works fine
       string date2= r["Last_Resurvey_Date"].ToString(); //works fine
       resultsLabel.Text = date1+"   "+date2;                 //works fine 

      //string dateString = r["First_Resurvey_Date"].ToString(); // Modified from MSDN [This doesn't work as sais invalid date]
        string format = "dd-MMM-yy";
        DateTime dateTime = DateTime.ParseExact("3/14/1985 12:00:00 AM", format, CultureInfo.InvariantCulture); [So, I have tried putting the data displayed as a string, but this doesn't work either]
 
         string date3= dateTime.ToString();
         NotesLabel.Text= date3;
         NotesLabel.Visible=true;

       //DateTime dt = Convert.ToDateTime(r["First_Resurvey_Date"]);
       //string format = "dd/MM/yy";
       //String date;
       //date = dt.ToString(format,DateTimeFormatInfo.InvariantInfo);

}

It says, "Exception: String was not recognized as a valid DateTime. "

My dates are currently displayed as "3/14/1985 12:00:00 AM", but I would like to have it as "14-Mar-85" or even "3/14/1985" would do as well.

Thanks in advance.

skhan · September 3rd, 2009, 01:28 PM

Now I could change the data format into how I wanted it to be but I don't understand how to insert that into the Datagrid to display as such??

Code:

[ds is my dataset]
foreach (DataRow r in ds.Tables[0].Rows)
{
     string dateString1 = r["First_Resurvey_Date"].ToString(); 
    DateTime dateTime=Convert.ToDateTime(dateString1);
    string [] dateStringArray= dateTime.GetDateTimeFormats();

    r["First_Resurvey_Date"]= dateStringArray[6]; [This doesn't work, I cannot just assign the specific value to a datagrid row/cell......I need help on how to do that] [dateStringArray[6], gives the date format as dd-MMM-YY, which is how I want to display my date in the application]
                       
    NotesLabel.Text= dateStringArray[6];
    NotesLabel.Visible=true;
 }
DataView view = ds.Tables[0].DefaultView;
resultsDatagrid.DataSource = view;
resultsDatagrid.DataBind();
resultsDatagrid.Visible = true;

Thank you .

Old Pedant · September 3rd, 2009, 05:20 PM

Kokoness showed you the answer in his post.

And WHY do you get the value out of the database *AS A STRING* if it's a DateTime value there???

Code:

foreach (DataRow r in ds.Tables[0].Rows)
{
    DateTime dt = r["First_Resurvey_Date"]; // do NOT convert it to string and then BACK to datetime!
    NotesLabel.Text = td.ToString("dd-MMM-yy"); 
    NotesLabel.Visible=true;
 }
...

Though why you would loop through all the rows and, in each row, set just the one label to the date, I don't get, at all.

I suspect that all you need is code to change the display of the value from the datatable *during* the binding to the datagrid. No?

But if you don't know how to do that, then maybe the right answer is to change your SQL query to get the date *AS A STRING* from Access.

Like this:

Code:

SELECT Right('0' & Day(yourfield),2) & '-' & MonthName(Month(yourfield),True) & '-' & Right(CStr(Year(yourfield)),2) AS formattedDate, ...

skhan · September 3rd, 2009, 05:33 PM

I had to do that stupid thing cause when I tried to do like the following:
DateTime dd=r["First_Resurvey_Date"];

It gave me the following error:
'System.Data.DataColumn.DataType' denotes a 'property' where a 'method' was expected

The only methods associated with r["First_Resurvey_Date"] were Equals(), getHashCode(), getType() and ToString(). So to get the DateTime value from column "First_Resurvey_Date", I had to do those meaningless steps. I could be completely wrong as I have very little knowledge of ASP.NET C#. But that's what I could do.

Yes I needed code to change the display of the value from the datatable *during* the binding to the datagrid. I don't understand how should I do that, so maybe I will try out the formatting in the SQL Query. Thank you for your suggestion. And sorry for my weird/stupid questions. It's not that I am not doing proper research, I am trying my best. It's just that I don't know everything about DataGrid properties to try out.

Old Pedant · September 3rd, 2009, 05:59 PM

To change the format, you have to specify custom binding with <%#...%> tags. I'm not really an ASP.NET person, so I'm hesitant to try to lead you here.

Ohh...and the problem is that you aren't using a "strongly typed dataset" so indeed you must *CAST* the Object (which is what r{"xxx"] gives you...a generic Object) to the proper type.

So *probably* just

Code:

    DateTime dt = (DateTime) r["First_Resurvey_Date"];

But not a C# person, either (Java and C++), so you might have to use an explicit convert function.