Display time datatype as AM/PM time in DataList

Question

<asp:Label ID="timeLabel" runat="server" Text='<%# Eval("time") %>' />

In this statement, "time" is a time datatype in my database. It displays as 16:00:00. I am trying to get this to display as 4:00 PM. I think this is pretty straight forward if I were working with a datetime datatype, but how can I convert this to datetime (to use the {HH.mm tt} formatting) or otherwise display with as AM/PM time.

Answer 1

Since T-SQL time is mapped with TimeSpan in CLR side, when you get this value from your database, it will be TimeSpan, not a DateTime.

And you can't represent a TimeSpan with AM or PM designators. These are only for DateTime representations. There is no such a thing a TimeSpan like; 4 hour time interval afternoon. That doesn't make sense, right?

Closest thing might be, getting this value from your database and add it to your DateTime.Today value with Add(TimeSpan) method overload. Then you can get it's string representation with .ToString("h:mm tt") method with a culture that doesn't have AMDesignator and PMDesignator as an empty string.

Step by step;

1. Get your time as a TimeSpan from your database.
2. Get a DateTime with DateTime dt = DateTime.Today.Add(time) method.
3. Show it's string representation with dt.ToString("h:mm tt", CultureInfo.InvariantCulture)

Answer 2

I solved this one with SQL instead of C# here. I did try to add the TimeSpan to a DateTime within the Eval() argument, but I kept getting a FormatException. Also, the expression was getting kind of ridiculous.

I tried this

<asp:Label ID="timeLabel" runat="server" Text='<%# DateTime.Today.Add(TimeSpan.Parse(Eval("time").ToString())).ToString("h:mm tt") %>' />

and this

<asp:Label ID="timeLabel" runat="server" Text='<%# Eval(DateTime.Today.Add(TimeSpan.Parse("time")).ToString("h:mm tt")) %>' />

It was considerably easier to modify the query to use

SELECT  CONVERT(varchar, [time], 100 ) AS [time]

I am happy with this result. Though, I would be curious to know if I was on the right track with the C# expressions. Thanks so much for the input.

Answer 3

<%#DateTime.Today.Add((TimeSpan)(Eval("Event_From_Time"))).ToString("H:mm tt")%>