May 2, 2012 at 9:30 am
Hi
I have a field in my table which has values in minutes.
I need to convert to hours. I am doing this as
CAST(col1/60 AS VARCHAR(6)) + ':' + CAST(col1%60.0 As VARCHAR(2))
This is working fine.
I need to convert this to TIME datatype because I need to Total these hours in SSRS report.
I am doing in the Group total. As it is originally VARCHAR from the query, Total is not working.
How can I do this
May 2, 2012 at 9:51 am
a2zwd (5/2/2012)
HiI have a field in my table which has values in minutes.
I need to convert to hours. I am doing this as
CAST(col1/60 AS VARCHAR(6)) + ':' + CAST(col1%60.0 As VARCHAR(2))
This is working fine.
I need to convert this to TIME datatype because I need to Total these hours in SSRS report.
I am doing in the Group total. As it is originally VARCHAR from the query, Total is not working.
How can I do this
i would do the group total on the actual column in min and then convert it to display in your report. make all the math on your minute column then once the math is done convert it to the final display output.
to convert it into hours there is a slightly better method:
DECLARE @Time INT = 82
SELECT CONVERT(char(8), DATEADD(MINUTE, @Time, ''), 114)
it will make sure to add in the leading 0 and colons in the right place.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 2, 2012 at 9:52 am
You can't cast that as TIME. You are talking hours and minutes.
Given your description take a look at this.
;with MyData(col1)
as
(
select 2932 union all
select 624
)
select CAST(col1/60 AS VARCHAR(6)) + ':' + CAST(col1%60 As VARCHAR(20))
from MyData
What kind of a TIME value would 48:52 be? TIME datatype is used to represent a time of day and that doesn't work. I think you should keep them separate and format later.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply