How to separate time from db datetime?

Question

Hi members,

Greetings of the day...

a time field in my Access db table, log in time is recorded as '10:30 AM' but when I fetch the record to a DGV, it shows as '30/12/1899 10:30 AM' !!!

I am surprising why the date is showing of the year 1899. the data type is date/time and format is Medium Date' the time data also being saved as mentioned above but the out put in DGV shows with date. I want it to be displayed as it is in the db.

I have found some functions that separates/extracts time from the date but the display remains the same.

DateTime dt = DateTime.Now;
string CONVdt = dsFetchAppo.Tables[0].Rows[0].ItemArray[3].ToString();

DateTime.TryParse(CONVdt, out dt);

string CONVtime = dt.ToString("H:mm tt");

please note that it's C# WinApp and database is MS Access.

Thank You

Mayank Jani

Amit Gupta · Answer

Please match your code with my below code, DefaultCellStyle is a part of datagridview in windows form. dataGridView1.Columns[1].DefaultCellStyle.Format = "h:mm tt"; Is that you had done? You will definitely get the desired output.

Rafnas T P · Answer

hi, you can try to change from your select query in that query replace your datetime filed with below line Format( datetimefieldname ,"hh:mm tt") in this change red color one with your datetime field for example of select query select Ename, Format( datetimefieldname ,"hh:mm tt") as attendtime,Gender from tablename

Mayank Jani · Answer

Hey, string dbTime = DateTime.Parse(dsEmpLogIn.Tables[0].Rows[0].ItemArray[2].ToString()).ToString("hh:mm tt"); this separates time from datetime with AM/PM. but I'm unable to display the result in DGV column. so, please help me here that how to put the dbTime in (suppose 3rd) column of a DGV for all records. Thanks.

Mayank Jani · Answer

Hey, string dbTime = DateTime.Parse(dsEmpLogIn.Tables[0].Rows[0].ItemArray[2].ToString()).ToString("hh:mm tt"); this separates time from datetime with AM/PM. but I'm unable to display the result in DGV column. so, please help me here that how to put the dbTime in (suppose 3rd) column of a DGV for all records. Thanks.

Amit Gupta · Answer

By default system has min and max date specified but it was 01/01/0001. Coming to your question, you can set cell format style at the below link https://stackoverflow.com/questions/648509/how-do-i-display-time-in-a-windows-forms-datagridview-bound-to-a-datatable

Mayank Jani · Answer

Hi, I have already been tried. it's not working. my questions are... 1. Why the system shows the date (30/12/1899) where there is not date in the table? and.. 2. How to put the values in the DGV column of time? because there can be uncertain number of records in the DGV. the function I have mentioned, extracts the time from the entire datetime and also arranges ascending but I can not put the records in the DGV column. now, what should I do? Should I write the function (if any) in the query which fetches the records form the db or should I separate the time from the date after the dataset fill and before display in the DGV? If you want to see the entire coding, please let me know. Thanks Mayank Jani

Mayank Jani · Answer

Hey Rafnas, the select query with Format is not working at all but, anyway, I have got my query solved. thanks for your kind of trying. Mayank.

Mayank Jani · Answer

Hiiii Yeaaaahhh... THAAAAAANNNNKKKKSSSSSS IT'S DONE. a tiny mistake and days wasted. I was doing... string dbTime = DateTime.Parse(dsEmpLogIn.Tables[0].Rows[0].ItemArray[2].ToString()).ToString("hh:mm tt"); //then dgvEmpLogIn.Columns["LogInTime"].DefaultCellStyle = dbTime; as I was thinking, first to convert the datetime into time and store into a veriable and then pass the veriable value to DGV. I have been done so many trial and error in this matter so, forgot which one is tried and which is not. :))) Thanks a lot... Mayank

Mayank Jani · Answer

I'm using it after the dataset filled with records and before to add the datasource to the DGV.

Amit Gupta · Answer

It should be used after databinding. I think you are using it before bibding that's why you are getting null exception.

Mayank Jani · Answer

Hi Rafnas T P DataType = Date/Time Format = Medium Date.

Mayank Jani · Answer

Hi Amit, not working... error "Object reference not set to an instance of an object." I want the time in AM/PM. there is no option in DefaultCellStyle > Format > DateTime.

Rafnas T P · Answer

hi, can you tell me how in access database that field save open access database and check is it saved in the format of datetime or only in time.

Amit Gupta · Answer

You can convert it using var time = dt.ToString("h:mm tt"); small h gives you time in 12 hrs whereas capital H gives you time in 24hrs