March 23, 2012 at 6:48 am
Hi,
I have a requirement to add columns (varchar) where data is separated with a colon (:). For e.g.
115:32 and 34:28
160:00 (hrs and minutes).
Thanks,
Paul
March 23, 2012 at 6:55 am
Well you have stated your requirement, as strange as it seems, but what is your question ? What assistance are you seeking ?
Looking for a better method to store datetime values? It seems as what you have been requested, is not the best method to store time, but then again you may not have any alternative.
March 23, 2012 at 7:08 am
bitbucket-25253 (3/23/2012)
Well you have stated your requirement, as strange as it seems, but what is your question ? What assistance are you seeking ?Looking for a better method to store datetime values? It seems as what you have been requested, is not the best method to store time, but then again you may not have any alternative.
I'll try to explain my requirement in detail:
There is a column(varchar) in a table which stores values in the format 00:00. I need to sum these values in such a way that it is stored in a HH:MM format. Say for e.g. there are these 2 values-
161:37 and 129:33. It should show the output as:
291:10 (converted in the format as HH:MM).
The problem is the column has a varchar datatype. I hope I have made it more clear.
Thanks.
March 23, 2012 at 9:00 am
Here is one way of doing what you want, but I must say it is not very efficient, but for the life of me I can not come up with another suggestion.
DECLARE @H1 VARCHAR(10)
DECLARE @H2 VARCHAR(10)
DECLARE @min-2 AS INT
DECLARE @Hrs AS INT
SET @H1 = '161:37'
SET @H2 = '129:33'
SET @min-2 = CAST(SUBSTRING(@H1,CHARINDEX(':',@H1)+1,DATALENGTH(@H1))AS INT)+CAST(SUBSTRING(@H2,CHARINDEX(':',@H2)+1,DATALENGTH(@H2))AS INT)
SELECT CAST(CAST(SUBSTRING(@H1,1,CHARINDEX(':',@H1)-1) AS INT)
+CAST(SUBSTRING(@H2,1,CHARINDEX(':',@H2)-1)AS INT)
+(@Min - (@Min%60))/60 AS VARCHAR(10))+':'+ CAST(@Min%60 AS VARCHAR(2))
Result:
291:10
March 23, 2012 at 9:17 am
What would make it clearer is if you took the time to provide us with the DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data for the table(s) (as INSERT INTO statements), the expected results based on the sample data, and what you have done so far to solve your problem so that we know where you are having problems and can help.
Please read the first article I reference below in my signature block regarding "Asking for help". It will show you what you need to provide and how to post it to get the best possible answers to your questions.
Remember, we can't see what you see unless you show us.
March 23, 2012 at 9:47 am
pwalter83 (3/23/2012)
Hi,I have a requirement to add columns (varchar) where data is separated with a colon (:). For e.g.
115:32 and 34:28
160:00 (hrs and minutes).
Thanks,
Paul
I would suggest to store your time in minutes in column of INT datatype. In this case you can easily use this time in any calculations. Formatting to HH:MM can be done when you output data to UI or other consumer.
At the end, you can have computed column which will "store" this in formatted way.
You will find, that storing it as formatted string (varchar), gives no much benefits, but guaranteed nightmare for any calculation...
March 23, 2012 at 10:14 am
bitbucket-25253 (3/23/2012)
Here is one way of doing what you want, but I must say it is not very efficient, but for the life of me I can not come up with another suggestion.
DECLARE @H1 VARCHAR(10)DECLARE @H2 VARCHAR(10)
DECLARE @min-2 AS INT
DECLARE @Hrs AS INT
SET @H1 = '161:37'
SET @H2 = '129:33'
SET @min-2 = CAST(SUBSTRING(@H1,CHARINDEX(':',@H1)+1,DATALENGTH(@H1))AS INT)+CAST(SUBSTRING(@H2,CHARINDEX(':',@H2)+1,DATALENGTH(@H2))AS INT)
SELECT CAST(CAST(SUBSTRING(@H1,1,CHARINDEX(':',@H1)-1) AS INT)
+CAST(SUBSTRING(@H2,1,CHARINDEX(':',@H2)-1)AS INT)
+(@Min - (@Min%60))/60 AS VARCHAR(10))+':'+ CAST(@Min%60 AS VARCHAR(2))
Result:
291:10
Thanks for your reply....However, I have to create a new column based on this calculation and use that in a report. Your code is correct but I just want to know how do I replace @min-2 ?
Something like this-
CAST(SUBSTRING(WeekHours,CHARINDEX(':',WeekHours)+1,DATALENGTH(WeekHours))AS INT)+CAST(SUBSTRING(WeekHours,CHARINDEX(':',WeekHours)+1,DATALENGTH(WeekHours))AS INT)
SELECT CAST(CAST(SUBSTRING(WeekHours,1,CHARINDEX(':',WeekHours)-1) AS INT)
+CAST(SUBSTRING(WeekHours,1,CHARINDEX(':',WeekHours)-1)AS INT)
+(WeekHours - (WeekHours%60))/60 AS VARCHAR(10))+':'+ CAST(WeekHours%60 AS VARCHAR(2)) as StandardHours
March 23, 2012 at 12:00 pm
@min-2 is simply functioning as a "place holder" or intermediate value. You could alter the previous code to
SET @min-2 = CAST(SUBSTRING(@H1,CHARINDEX(':',@H1)+1,DATALENGTH(@H1))AS INT)+CAST(SUBSTRING(@H2,CHARINDEX(':',@H2)+1,DATALENGTH(@H2))AS INT)
INSERT INTO NewColumn -- Inserted new statement
SELECT CAST(CAST(SUBSTRING(@H1,1,CHARINDEX(':',@H1)-1) AS INT)
+CAST(SUBSTRING(@H2,1,CHARINDEX(':',@H2)-1)AS INT)
+(@Min - (@Min%60))/60 AS VARCHAR(10))+':'+ CAST(@Min%60 AS VARCHAR(2))
]
March 26, 2012 at 2:27 am
bitbucket-25253 (3/23/2012)
@Min is simply functioning as a "place holder" or intermediate value. You could alter the previous code to
SET @min-2 = CAST(SUBSTRING(@H1,CHARINDEX(':',@H1)+1,DATALENGTH(@H1))AS INT)+CAST(SUBSTRING(@H2,CHARINDEX(':',@H2)+1,DATALENGTH(@H2))AS INT)INSERT INTO NewColumn -- Inserted new statement
SELECT CAST(CAST(SUBSTRING(@H1,1,CHARINDEX(':',@H1)-1) AS INT)
+CAST(SUBSTRING(@H2,1,CHARINDEX(':',@H2)-1)AS INT)
+(@Min - (@Min%60))/60 AS VARCHAR(10))+':'+ CAST(@Min%60 AS VARCHAR(2))
]
Thanks for your reply....however, this is not working for me, I get errors when I run your code...I will explain it more clearly....I have a column 'WeekHours' which has a datatype varchar and which stores values in the format '00:00'. I need to Sum all these values in the time format 'HH:MM'. For e.g. the values in the rows are-
35:00
35:30
34:50
32:25
38:27
I need to Sum all these values and store them so that the result for the above example shows as- 176:12 (HH:MM). Is the explanation more clear now ?
I am really stuck with this and don't know how to proceed.
Thanks.
March 26, 2012 at 4:31 am
Not sure if this example is any more efficient than the one provided by bitbucket but it is an alternate:
DECLARE @times TABLE ([hh:mm]VARCHAR(12))
INSERT INTO @times
SELECT '115:32'
UNION ALL SELECT '34:28'
;WITH Times AS (
SELECT SUM(CAST(SUBSTRING([hh:mm], 1, CHARINDEX(':',[hh:mm])-1) AS INT)) as hh
,SUM(CAST(SUBSTRING([hh:mm], CHARINDEX(':',[hh:mm])+1, LEN([hh:mm])) AS INT)) as mm
FROM @times
)
SELECT CAST(hh + mm/60 AS VARCHAR)+ ':' + RIGHT('00'+CAST(mm%60 AS VARCHAR),2)
FROM Times
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 26, 2012 at 8:18 am
Could someone please have a look at my query, I am stuck with this one for a long time now...
I have a column 'WeekHours' which has a datatype varchar and which stores values in the format '00:00'. I need to Sum all these values in the time format 'HH:MM'. For e.g. the values in the rows are-
35:00
35:30
34:50
32:25
38:27
I need to Sum all these values and store them so that the result for the above example shows as- 176:12 (HH:MM). Is the explanation more clear now ?
I am really stuck with this and don't know how to proceed.
Thanks.
March 26, 2012 at 8:58 am
You did say previously:
Thanks for your reply....However, I have to create a new column based on this calculation and use that in a report. Your code is correct but I just want to know how do I replace @min-2 ?
I explained that @min-2 was a "place holder" i.e. a variable to hold intermediate data.
Now:
Did you test the code posted by dwain.c?
If yes what result(s) did you obtain ?
March 26, 2012 at 10:06 am
bitbucket-25253 (3/26/2012)
You did say previously:Thanks for your reply....However, I have to create a new column based on this calculation and use that in a report. Your code is correct but I just want to know how do I replace @min-2 ?
I explained that @min-2 was a "place holder" i.e. a variable to hold intermediate data.
Now:
Did you test the code posted by dwain.c?
If yes what result(s) did you obtain ?
Thanks Ron !
actually I have created a query and this piece of code needs to be included within that. I don't know how to convert your code to include it within the query. The query is as below (your code is in bold):
Select
max(datepart(yyyy,s.[Date])) as [Year],
/*
CAST(SUBSTRING(WeekHours,CHARINDEX(':',WeekHours)+1,DATALENGTH(WeekHours))AS INT)+CAST(SUBSTRING(WeekHours,CHARINDEX(':',WeekHours)+1,DATALENGTH(WeekHours))AS INT)
SELECT CAST(CAST(SUBSTRING(WeekHours,1,CHARINDEX(':',WeekHours)-1) AS INT)
+CAST(SUBSTRING(WeekHours,1,CHARINDEX(':',WeekHours)-1)AS INT)
+(WeekHours - (WeekHours%60))/60 AS VARCHAR(10))+':'+ CAST(WeekHours%60 AS VARCHAR(2)) as tt,
*/
CONVERT(varchar(6), (sum((datepart(hour,Timenetin) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin))) / 3600) + ':' + RIGHT('0' + CONVERT(varchar(2),
(sum((datepart(hour,Timenetin) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin))) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,Timenetin) * 3600)) + sum(datepart(minute,Timenetin) * 60) +
sum(datepart(second,Timenetin))) % 60), 2) as [Actual Hours],
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin) * 1)) - (25200* count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end)))/(3600)) As Hrs_Diff,
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin) * 1)) - (25200* count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end))) %(3600)/60) As Min_Diff,
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin) * 1)) - (25200* count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end))) %60) as Sec_Diff
from StaffDay s
inner join Staff st
on s.Staff_id = st.Staff_id
where s.Dayname NOT IN ('Sun','Sat')
and s.Bank_holiday_flg = 'N'
and datepart(mm,s.[Date]) IN ('01','02', '03','04','05','06','07','08','09','10','11','12')
and datepart(yyyy,s.[Date]) = '2012' --IN (@Year)
and st.active = 'Y'
group by datepart(mm,s.[Date]) ,s.Cardholder_name
order by datepart(mm,s.[Date]) asc
March 26, 2012 at 11:52 am
I have to ask... Why are you doing all of this conversion? Did you develop this app or dis someone else? Can you change the way data is stored? This is just plain poor design... So, if you can change it, so it. Else, I would use some type of ETL to copy this data to another table with a format that will work for you.
Jared
CE - Microsoft
March 26, 2012 at 10:36 pm
pwalter83 (3/23/2012)
Hi,I have a requirement to add columns (varchar) where data is separated with a colon (:). For e.g.
115:32 and 34:28
160:00 (hrs and minutes).
Thanks,
Paul
Paul,
For future posts, please see the article at the first link in my signature line below. It'll help us help you more quickly.
First, we need some test data. I figure 1,000 different times will do. This is just test data and is not a part of the solution.
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('TempDB..#YourTable','U') IS NOT NULL
DROP TABLE #YourTable
;
GO
--===== Create a table with a column of VARCHAR times
WITH
cteGenHHHMM AS
(
SELECT TOP (1000)
SomeDateTime = RAND(CHECKSUM(NEWID())) * 10 + CAST(0 AS DATETIME)
FROM sys.all_columns ac1,
sys.all_columns ac2
)
SELECT WeekHours = CAST(DATEDIFF(hh,0,SomeDateTime) AS VARCHAR(10)) + ':'
+ RIGHT(CONVERT(CHAR(8),SomeDateTime,108),2)
INTO #YourTable
FROM cteGenHHHMM
;
--===== Show what we've created as a test table
SELECT * FROM #YourTable
;
Here's some relatively simple code to solve the problem.
--===== Now show one way to sum all those Varchar times
WITH
cteToDateTime(DT) AS
(
SELECT DATEADD(mi,SUM(LEFT(WeekHours,CHARINDEX(':',WeekHours)-1)*60 + RIGHT(WeekHours,2)),0)
FROM #YourTable
)
SELECT CAST(DATEDIFF(hh,0,DT) AS VARCHAR(10))
+ SUBSTRING(CONVERT(CHAR(8),DT,108),3,3)
FROM cteToDateTime
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply