All formats other than 't' return #Error for Time fields in Report Builder

Question

I have a SQL Time Field in a Report Builder table that I would like to format. In this case, I would like to format the field in [SlotTime] in hh:mm tt format.

When I run the report, I get the following:

I tried to set the format of SlotTime as follows:

When I run the report, all SlotTime values show #Error.

Notes

1. Since the [SlotTime] needs to be in the right order in the table, I cannot use a FORMAT(SlotTime, 'hh:mm tt') in the SQL Query as this will produce a VARCHAR field and not a TIME field.

The schedule table rows start at 01:00 PM instead of 08:00 AM.

2. If a DATETIME field is used, the columns for each consecutive day will be staggered by 24 hours. When a DATETIME row is used, 8:00 AM is really a row for 2023-08-31 08:00:00. The row for 2023-09-01 8:00 AM will not be side-by-side with the row for 2023-08-31 08:00:00. You will have to scroll down through 24 hours or row entries to see the next day.

3. A small snippet of the SlotDate, SlotTime, and SlotStatus is shown below.

4. CAST the TIME column to a DATETIME Column works.

Solution

1. CAST the TIME column as a DATETIME. This will serve as the resulting DATETIMEs have year, month, and day as 1900, 01, and 01 respectively.

Answer 1

There may be a better way of doing this but this way does work.

I created some simple data with 3 days and 8 slots per day and added some arbitrary 'amount' column.

Forgive the awful column names...

declare @t table (dt date, tm time, amount int)

INSERT INTO @t VALUES 
('2023-08-29', '08:00:00', 1), ('2023-08-29', '08:15:00', 2), ('2023-08-29', '08:30:00', 3), ('2023-08-29', '08:45:00', 4), ('2023-08-29', '09:00:00', 11), ('2023-08-29', '09:15:00', 12), ('2023-08-29', '09:30:00', 13), ('2023-08-29', '09:45:00', 14),
('2023-08-30', '08:00:00', 11), ('2023-08-30', '08:15:00', 12), ('2023-08-30', '08:30:00', 13), ('2023-08-30', '08:45:00', 14), ('2023-08-30', '09:00:00', 111), ('2023-08-30', '09:15:00', 112), ('2023-08-30', '09:30:00', 113), ('2023-08-30', '09:45:00', 114), 
('2023-08-31', '08:00:00', 21), ('2023-08-31', '08:15:00', 22), ('2023-08-31', '08:30:00', 23), ('2023-08-31', '08:45:00', 24), ('2023-08-31', '09:00:00', 211), ('2023-08-31', '09:15:00', 212), ('2023-08-31', '09:30:00', 213), ('2023-08-31', '09:45:00', 214) 

SELECT *, dttm = cast(dt as datetime) + cast(tm as datetime) FROM @t

As you can see, in the final select statement, I have added a column dttm (datetime) which creates a datetime column from the individual date (dt) and time (tm) columns.

Now we have this it's really simple...

Add a matrix, drop the dt fields onto the column placeholder, the tm field onto the row placeholder and the amount field on to the data placeholder. This sets up almost everything.

Now swap the tm field in the first column and choose dttm instead.

So not we have columns grouped by date, rows grouped by rime but we are displaying the combined datetime (dttm) field for the row headers.

Finally, format the dttm textbox as hh:mm tt

Here's the final design and output.