CODEWITHSUNDEEP
sqlsql-serversql-server-2008reporting-services
Alex Gordon
Alex Gordon

Reputation: 60711

Incrementing hours and days

I am building a report in SSRS.

In design mode it looks like this:

enter image description here

tat2 are values 1 through 192 and appear on the report like this:

1
2
3
...
192

I would like to know if there's a way to instead do something like this:

DAY 1 12:00AM
DAY 1 1:00AM
DAY 1 2:00AM
...
DAY 7 9:00PM
...
DAY 8 12:00AM

In other words, I would like to turn the numbers 1 through 192 into hours and days.

Upvotes: 0

Views: 107

Answers (2)

Charleh
Charleh

Reputation: 14012

You could use Date.AddHours() for this - just create a new Date that's the start of any year and use 

Date.AddHours(Fields!YourNumericField.Value)

This way you get rolling hours - will you ever have more than 192? What's the maximum range, as this would roll-over at 365. You could just mix and match and do an expression though like:

=Math.Ceiling(Fields!YourNumericField.Value / 24) & SomeDate.AddHours(Fields!YourNumericField.Value)

Something like that

I don't have SSRS on this machine to test though :P

Edit:

Ok so to get the base date you can use new DateTime(year, month, day)

http://msdn.microsoft.com/en-us/library/system.datetime.aspx

So the expression

="DAY " & Math.Ceiling(Fields!tat2.Value / 24) & " " & format(new DateTime(2000, 1, 1).AddHours(Fields!tat2.Value), "hh:mm tt")

This should give:

DAY 1 10:45 AM

Should work - if you want to change the format of the 10:00AM bit check this reference:

http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

"HH:mm" gives you 24 hour time + minutes e.g. 23:54

"hh:mm tt" is 12 hour e.g. 12:00 PM

Have a play

Upvotes: 3

Ken White
Ken White

Reputation: 125669

This can be easily done in the underlying query - not sure about doing it in SSRS:

SELECT 
  Tat2 / 24 + 1 as Day, 
  CAST(Tat2 % 24 AS CHAR(2)) + ':00 ' +
    CASE WHEN Tat2 % 24 > 12 then 'PM' else 'AM' end as AMPM
FROM YourTable

This won't, of course, handle more than 365 days, because it doesn't months or years.

Upvotes: 1

Related Questions