2

I have a DataTable with a DataColulmn of TimeSpan type. It gets filled with a time column in a SQL Server query, and it shows as a 24 hr format on screen. I wanted to show it as a 12 hr format + am/pm using an expression column, but coulnd't do it. I tried using convert to the column to handle the string, but it shows a strange format such as PT20H15 (that would be 20:15 or 8:15 pm). As the position of time and hour changes if the hour is one or two digits, and if hour is 00 then it get removed, string handling seems like too complicated. I tried just comparing the column value like "HourCol > 12" but that raises an error regarding comparing timespan to int, so I failed again. Does anyone have a different suggestion?

Thanks in advance.

Alejandro B.
  • 4,807
  • 2
  • 33
  • 61
  • ASP.NET, WinForms, WPF, or other? It matters in how you accomplish this. How a DateTime value is displayed is dependent on the program actually displaying it, more than on the underlying value. You CAN convert it to a varchar in teh SQL Query you use to get it out, but that's not always the best way to do it. – David Aug 22 '12 at 17:46
  • @David I show the data on a grid on a ASP.NET page. Users can edit the data on separate controls, and then I save the values back to SQL Server. I rather have an expression column so I don't have to make a double convertion, just update the original column on my datatable... if possible. – Alejandro B. Aug 22 '12 at 17:54

1 Answers1

2

it shows a strange format such as PT20H15

That's not a strange format at all. It's an ISO-8601 format for a period, which is fairly reasonable for a TimeSpan.

I wanted to show it as a 12 hr format + am/pm

That sounds like you're using a TimeSpan for a "time of day", which is far from ideal to be honest. I know it's what DateTime.TimeOfDay gives, but that's just because the framework doesn't have a "time only" type, worse luck.

Does anyone have a different suggestion?

Can you have a computed column which is basically some fixed date (or even DateTime.Today) + the timespan value, and format that using a hh:mm tt format? (Could you manually overwrite the values yourself in the DataTable, perhaps, rather than using a computed expression?)

Another alternative would be to change your SQL query to do the conversion to a date/time value, rather than doing it in the DataTable.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194