My datagridview binded with MS Access Database displays wrong time format-CodePudding

I am trying to print pdf report using itextsharp pdf and all the values are correct except the values from column "Date" and "Time". The date and time is displayed in a format like "dd/MM/yyyy tt hh:mm:ss" for eg. Date is: 08/10/2021 AM 12:00:00 and Time is: 30/12/1899 AM 07:46:37.

I have done everything mentioned below:

Inserting the date value from the datetimepicker as datetimepicker.Value.ToString("dd/MM/yyyy") and time value to be DateTime.Now.ToString("hh:mm:ss tt")
My datatype for Date and Time column in MS Access database is Date/Time

My database is showing correct formatted value and datagridview has incorrect format of Time column and generated pdf report have incorrect date and time format. My only question is how can this be possible as database consist of correct data but after displaying it in datagridview the format changes and even after printing it. Below I have attached images that will help you get the scenario.

Datatype for the column from database↓

Values in database↓

Values in Datagridview↓

CodePudding user response：

The epoc of data type Date in Access is 1899-12-30, and as you display both date and time, that date is displayed as well.

In .Net you can apply the format string "T" to display hour-minute-second only. For example:

DateTime time = new DateTime(1899, 12, 30, 8, 12, 16);
Console.WriteLine(time.ToString("T"));
// 08:12:16

CodePudding user response：

you can try to set the DefaultStyle property like this

dataGridView1.Columns["YourColumn"].DefaultCellStyle.Format = "HH:mm:ss";

Or set it in the designer, whatever you like most.

In your database your Time column seems to have only a time value, but it has not. It uses the lowest possible date value, which is 30/12/1899 for an Access Database.
This happens when you only feed that column with a time, and no date. Acces (and other databases also) will simply use their lowest possible date value to fill up the missing date value.
The DataGridView therefor has no choice but to also show the date along with its time.

The property above will simply instruct the DataGridView to not show the date part, only the time part.

If you need to display the time in code in C#, you could use this

YourDateTimeVariable.ToString("T");

But, since you are storing both date and time in datetime columns, why do you store them seperate ?
You could just store them in one column, and show/use only the part you need.