Member Avatar for steven8579
steven8579 11 Light Poster

I have an sql database names SubJobs with a column named Date. The data type is Time(7). I have a datagridview named DataGridView1. I want it to display a time like 04:30 PM. I’ve tried the following command to format the column but it gives me an error saying that it can’t be converted.

dataGridView1.Columns[3].DefaultCellStyle.Format = "hh:mm tt";

Any help would be appreciated!

Thanks,
Steven

  • 1 Contributor
  • 0 Replies
  • 438 Views

Dani AI

Generated

— The underlying cause is that SQL Server TIME maps to System.TimeSpan in ADO.NET, while the format string hh:mm tt is a DateTime/AM‑PM pattern. TimeSpan formatting does not understand the AM/PM designator, so applying a DateTime-style format to a TimeSpan triggers the conversion/formatting error.

Three practical fixes (pick one that fits the project):

  1. Convert at the query level so .NET receives a DateTime or a preformatted string. Examples:
    
-- returns a DateTime (date will be 1900-01-01)
SELECT CAST([Date] AS datetime) AS DateAsDateTime FROM SubJobs;

-- or, on SQL Server 2012+, return a formatted string
SELECT FORMAT([Date], 'hh:mm tt') AS TimeDisplay FROM SubJobs;

Note: FORMAT is convenient but slower for large result sets; CAST gives a DateTime value that the DataGridView can format with standard DateTime format strings.

2) Convert in the data layer (DataTable) and add a display column:

dataTable.Columns.Add("TimeDisplay", typeof(string));
foreach (DataRow r in dataTable.Rows)
{
var ts = (TimeSpan)r["Date"];
r["TimeDisplay"] = DateTime.Today.Add(ts).ToString("hh:mm tt");
}

Bind the grid to show TimeDisplay.

3) Format on the fly in the DataGridView using CellFormatting (no schema change):

private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
if (dataGridView1.Columns[e.ColumnIndex].Name == "Date" && e.Value is TimeSpan ts)
{
e.Value = DateTime.Today.Add(ts).ToString("hh:mm tt");
e.FormattingApplied = true;
}
}



Quick checks and tips: verify the column type with `dataTable.Columns["Date"].DataType` (should be System.TimeSpan). If the values are truly "time of day", converting to DateTime is appropriate; if they are durations, use TimeSpan formatting (for example `ts.ToString(@"hh\:mm")`) without AM/PM.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.