February 13, 2009 at 1:38 pm
Hello:
I've built an SSRS report that pulls data directly from an ODBC data source. The SQL query pulling the data works fine, and everything's showing up perfectly except for two time fields, TimeFrom and TimeTo. Both fields appear as military time, and in trying to convert them to 12-hour format, I discovered that their datatype is apparently TimeSpan. This doesn't make any sense to me, as they're two distinct fields with only one value in each: TimeFrom has the start time, and TimeTo has the end time. There's no "span" as far as I'm aware. The issue I'm having is that it won't let me convert the TimeSpan type to anything, including DateTime and String. Does anyone know how I can get around this? It's holding up an important project, so any insights would be very gratefully received! 😀
Thanks in advance!
February 13, 2009 at 3:10 pm
What is the actual source of the data? Is it Access, SQL Server, Oracle, MySQL?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 13, 2009 at 3:43 pm
If you are in SQL Server 2008 you could import your data and run it in SSRS because the .NET TimeSpan converts to the new Time Data Type. Check below for the SQL Server Data Types and .NET Data Types.
http://msdn.microsoft.com/en-us/library/ms131092.aspx
Kind regards,
Gift Peddie
February 16, 2009 at 7:59 am
Hello:
Thanks for the responses! Jack, the data is coming from a UNIX-based custom database that I inherited--it's not in any of the DBMS formats with which I'm familiar. And Gift, unfortunately, we only have SQL Server 2005 at this point. 🙁
February 16, 2009 at 8:45 am
This is just like the code you are running from ODBC install SQL Server 2008 Express and run your code from it. The other option is to write .NET function and use the function box.
Kind regards,
Gift Peddie
February 16, 2009 at 9:04 am
What does the data actually look like in SSRS? You should be able to use a custom function to format it appropriately.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 16, 2009 at 9:08 am
Hello:
Gift, unfortunately I'm very new at this, and I know nothing at all about .NET--I don't even know what you mean by "function box." Please excuse my ignorance! :unsure:
Jack, the data appears as a military time-style stamp, e.g., 15:00:00. I've tried to convert it multiple ways, but it always throws an error saying that it can't be converted from datatype "TimeStamp" to datatype "String" or "DateTime" or anything I've tried.
February 16, 2009 at 10:09 am
I was actually talking about the Code box in the Report properties at the top of BIDs under Reports you could use VB.NET code in that box.
I also see DateDiff as one of the Date Functions in the Expression box so you could try that if you use DateTime instead of TimeSpan.
Kind regards,
Gift Peddie
February 16, 2009 at 11:23 am
Hi, Gift:
I actually really would like to use the DateTime datatype, but the problem is that the existing datatype is TimeSpan and it won't convert. I don't want a time span at all; I just want to show a single time (StartTime or EndTime) without any calculation. For some reason, even though the data shows up just looking like a military-style timestamp (like 17:30:00), it's being read in SSRS as having a TimeSpan datatype. What I want is to just show it as a DateTime type (like 5:30 pm).
February 16, 2009 at 11:51 am
Here is some help from Microsoft online see if you can use it.
http://www.windows-tech.info/15/4372d6bce7ab896a.php
Kind regards,
Gift Peddie
February 16, 2009 at 12:33 pm
Okay, well, I came upon this code somewhere:
Dim ts As TimeSpan
Dim dt As DateTime = DateTime.MinValue.Add(ts)
Dim s As String
s = ts.ToString()
st = dt.ToString("hh:mm tt")
...but I don't know enough about proper syntax to know how to use it. I put it in the Code box and received an error saying that a declaration is expected for line 3 of the code. What declaration am I supposed to be using?
February 16, 2009 at 12:35 pm
Nice find on the Link, Gift. That should work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 16, 2009 at 12:51 pm
Thanks to you guys for bearing with me, but that code didn't work--it throws the same error, "Conversion from type 'TimeSpan' to type 'Date' is not valid." I don't know if that particular code is what I want anyway, because I don't want a TimeSpan at all. Basically I want this:
Clay Sculpture 101
Professor Banks
Monday, February 16, 2009
5:30 pm to 8:00 pm
I have this in a table in SSRS and everything works except the times. I want the time row to be formatted like this: =(Fields!StartTime.Value & " to " & Fields!EndTime.Value). I don't want any calculation, just the value of one field, then "to", then the value of the other field. But when I try to use the code above, I get the error "Operator '&' is not defined for type 'TimeSpan' and string " to "." So basically I can't do anything with these fields because their datatype is TimeSpan. I need to know how to convert each individual field from TimeSpan to DateTime or String or anything other than TimeSpan.
February 16, 2009 at 12:52 pm
You need to use the two sets of code in the post before the last post in the link I posted.
Kind regards,
Gift Peddie
February 16, 2009 at 12:59 pm
Hi, Gift:
Again, thanks so much for your help. That was the code that I used, but I can't declare parameters because this report needs to have only one parameter, OrderNumber. There can be multiple times on an order, so I can't have any parameters referring to the times...unless I don't understand parameters correctly, which is possible.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply