datetime HOUR function format

I am using reporting services to make a matrix. The row value is the date portion of DateIn. The value is a count of transactions. The column type is the problem. It is the hour part of the timein value.

I got it from the database like this:

{fn HOUR(dbo.[Transaction].[TimeIn])} AS Hour

This works, but gives 24 hour time (and only the hour part, so it looks like 10, 11, 12, 13, 14, etc.)

I want it to look like 10:00 AM, 11:00 AM, 12:00 PM, 1:00, PM, etc.

I have read several books, checked online books, tried format functions... and I'm going nuts. This should be so simple- how do I format this so a human can read it? Thanks

If you need the database to do the conversion, then you can set the format code of the textbox to "t" and use the following expression.

=CDate(Fields!Hour.Value & ":00")

If you can use the raw date value from the database, then you can just set the format code of the textbox to "t". If you are grouping on only the hour, then you can still just get the raw date value from the database and use =Fields!TimeIn.Value.Hour as the group expression.

DateDiff problem

I have a db with a table that records details about order transactions. The
main columns I am concerned about are the "Transacton_Type" column and the
"Date_Time" column. Each order has more than one transaction/rows in the
table (ie. Pending, Shipped, etc...), with each transaction having its' own
time stamp (as opposed to a table with a Pending column and a Shipped
column). How can I use the DateDiff function to give me the difference
between the Pending date and Shipped date for each Order (uniquely referenced
in a "P.O." column) if the timestamp for each transaction exists in the same
column. Is this even possible?
There are a couple of ways to do this, and they should be efficient if
there are supporting indexes:
select
T1.[P.O.],
T1.Date_Time as PendingDate,
T2.Date_Time as ShippedDate,
datediff(day, T1.Date_Time, T2.Date_Time) as LagTime
from yourTable T1
join yourTable T2
on T1.[P.O.] = T2.[P.O.]
where T1.Transaction_Type = ''Pending'
and T2.Transaction_Type = 'Shipped'
-- add where clause to restrict to a certain customer, for example.
The second way shows the pending, shipped, etc., dates as separate
columns. You can create the inner query here as a view, or adapt this
in other ways for individual queries.
select [P.O.], datediff(day,PendingDate, ShippedDate) as LagTime
from (
select
[P.O.],
max(case when Transaction_Type = 'Pending' then Date_Time end) as
PendingDate,
max(case when Transaction_Type = 'Shipped' then Date_Time end) as
ShippedDate,
.. other dates you need
from yourTable
-- restrict to a P.O. either here or ..
group by [P.O.]
) T
-- .. here (other place to restrict for limited results)
Steve Kass
Drew University
Strugglin' wrote:

>I have a db with a table that records details about order transactions. The
>main columns I am concerned about are the "Transacton_Type" column and the
>"Date_Time" column. Each order has more than one transaction/rows in the
>table (ie. Pending, Shipped, etc...), with each transaction having its' own
>time stamp (as opposed to a table with a Pending column and a Shipped
>column). How can I use the DateDiff function to give me the difference
>between the Pending date and Shipped date for each Order (uniquely referenced
>in a "P.O." column) if the timestamp for each transaction exists in the same
>column. Is this even possible?
>