May 27, 2011 at 11:27 am
I have hours coming into a stored procedure with definition of nvarchar(24). I am getting this from an application already in place. Now I need to sum the hours converting to minutes and need to convert to datetime. It will not let me. It was once defined as datetime and worked, but the data changed since I calculated minutes to hours for display and I used a function already in place. I keep getting an error 'Conversion failed when converting date and/or time from character string'.
Nothing I do to convert back works. I have tried float, int, and I just keep getting the same error no matter when I try to convert to.
Any suggestions?
DMR
May 27, 2011 at 11:32 am
Difficult to answer your question without some sample data and desired results.. could you post same? For ease of posting readily consumable data please click on the first link in my signature block for a brief "how to"
May 27, 2011 at 11:52 am
Thanks.
Input from a database table:
WorkTimes
Hoursworked nvarchar(24) ( sample of hours:minutes data- 04:28)
create table #ExtraHoursRevised (
hoursworked datetime)
insert into #ExtraHoursRevised
select Hoursworked
from WorkTimes
I need the datetime definition to sum the hours further in the procedure.
It will let me convert to float and convert to minutes if it is defined as datetime.
May 27, 2011 at 12:13 pm
ruff if you only have hours and minutes, how are you going to get a datetime? you need a base/starting point...is that jan 1 of this year, for example?
you could sum them up and get , say elapsed time in minutes or something...is that what you are after?
/*--sample results
AllTheMinutesInTheHours AllTheMinutes TotalMinutes
16860 348 17208
*/
With MySampleData (HoursWorked) AS
(
SELECT CONVERT(nvarchar(24),'26:11') UNION ALL
SELECT '21:09' UNION ALL
SELECT '25:49' UNION ALL
SELECT '56:52' UNION ALL
SELECT '28:52' UNION ALL
SELECT '02:21' UNION ALL
SELECT '53:02' UNION ALL
SELECT '24:49' UNION ALL
SELECT '35:54' UNION ALL
SELECT '11:49'
),
MyDataParsedNicely AS
(
SELECT
CONVERT(int, LEFT(HoursWorked,2)) As TheHours,
CONVERT(int, RIGHT(HoursWorked,2)) As TheMinutes
FROM MySampleData
)
SELECT
SUM(TheHours) * 60 As AllTheMinutesInTheHours,
SUM(TheMinutes) As AllTheMinutes,
SUM(TheHours) * 60 + SUM(TheMinutes) As TotalMinutes
FROM MyDataParsedNicely
--Elapsed Time?
Lowell
May 27, 2011 at 2:18 pm
Thanks.
Actually this is working but there was a change to the hours format and some hours have a 0 in front and some do not. ie 04:15 and 3:15. This probably made the sp crash in the 1st place.
May 27, 2011 at 3:05 pm
Unless you have this figured I have to agree with BitBucket. I can come up with 3-4 different potential things you might be trying to do. If you provide some actual sample data and usable ddl we can knock this out pretty simply.
_______________________________________________________________
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/
May 27, 2011 at 4:15 pm
Well, its weird actually the above format is not the cause. It seems to be only happening with a specific row or type of row. The logic is the same for other conditions that pull the same kind of data. It is stopping on this 0:59 hours and giving this message
"Conversion failed when converting date and/or time from character string"
Another row that has this 0:37 with the same logic and definitions is getting through. I have the destination temp table defined as datetime for that and the source coming in is defined as varchar(24).
Could that row be corrupted? Its just strange that it isn't happening to any other of the rows. When I comment out that insert to the table. The entire procedure runs through fine.
I am at a loss as to what to do. Is there a way to test that to see what is going on with that one row?
May 27, 2011 at 5:25 pm
Here is what we expected from you, remember you are closest to the problem and we are not, so you may have something that you are aware of and we can not be for example, converting what you have posted to this time:
CREATE TABLE #WorkTimes(Hoursworked nvarchar(24))
INSERT INTO #WorkTimes
SELECT '04:28' UNION ALL
SELECT '0:59' UNION ALL
SELECT '0:37'
create table #ExtraHoursRevised (hoursworked datetime)
insert into #ExtraHoursRevised
select Hoursworked
from #WorkTimes
SELECT * FROM #ExtraHoursRevised
Result:
hoursworked
1900-01-01 04:28:00.000
1900-01-01 00:59:00.000
1900-01-01 00:37:00.000
Ergo no errors .. now can you post the row you have commented out, and the complete table definition, along with your code.
Could that row be corrupted? Its just strange that it isn't happening to any other of the rows. When I comment out that insert to the table. The entire procedure runs through fine.
I am at a loss as to what to do. Is there a way to test that to see what is going on with that one row?
And the answer to your question is "Yes", but without having all the information why we would be guessing .... so please help us to help you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply