August 14, 2008 at 3:16 am
Hi All
Running this code:
SELECTDATEDIFF(N,Data.TimeFrom,DATEADD(mi,1,Data.TimeTo)) TimeDifference
FROM
(
SELECTCAST (CONVERT(nvarchar(5),HC_IH_TD_ACTUAL_TIME_TO, 108) AS datetime) TimeTo,
CAST (CONVERT(nvarchar(5),HC_IH_TD_ACTUAL_TIME_FROM, 108) AS datetime) TimeFrom
FROM dbo.HC_IH_TIMESHEET_DETAIL
) AS Data
Gives me this error:
(45804 row(s) affected)
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
Not quite sure what ive messed up. It ran once and gave me the data i needed, but come to run it again and i get that!
If i use SELECT TOP 25000 it works, but if i go over 50000 (and there will be many many thousand's more records) it fails to work. Using it without TOP as above fails no matter what.
Scott
August 14, 2008 at 3:48 am
Don't use CAST for varchar-datetime conversions.
Always use CONVERT with style specified.
_____________
Code for TallyGenerator
August 14, 2008 at 3:51 am
If i use this:
SELECT DATEDIFF(N,Data.TimeFrom,DATEADD(mi,1,Data.TimeTo)) TimeDifference
FROM
(
SELECTCONVERT(nvarchar(5),HC_IH_TD_ACTUAL_TIME_TO, 108) TimeTo,
CONVERT(nvarchar(5),HC_IH_TD_ACTUAL_TIME_FROM, 108) TimeFrom
FROM dbo.HC_IH_TIMESHEET_DETAIL
) AS Data
I get the same problems.
August 14, 2008 at 4:00 am
What are the datatypes of the columns used in the query?
_____________
Code for TallyGenerator
August 14, 2008 at 4:03 am
The table is made up of an imported file, and for a reason we cant figure out (and we cant change it) the times are imported into a NVARCHAR(5) field type.
August 14, 2008 at 4:07 am
Then you need first EXPLICITLY convert to to datetime acording to the format used in the file.
Apparently you convert FROM datetime indtead of converting TO datetime.
_____________
Code for TallyGenerator
August 14, 2008 at 4:13 am
Hmmm, not sure i understand what you mean. I thought this:
CONVERT(nvarchar(5),HC_IH_TD_ACTUAL_TIME_TO, 108)
Converted the value from NVARCHAR to style 108, which is datetime.
August 14, 2008 at 4:45 am
No it should be CONVERT(datetime, HC_IH_TD_ACTUAL_TIME_TO, 108)
Always read manuals before using tools.
In this case it's BOL ("help" for SQL Server)
_____________
Code for TallyGenerator
August 14, 2008 at 4:51 am
Then the problem is somewhere else, using the line you typed above i still get the same error.
Need to try and find out what's causing it, it obviously is something im not seeing.
August 14, 2008 at 4:54 am
Then what makes you think 108 is appropriate style for the format used in your file?
_____________
Code for TallyGenerator
August 14, 2008 at 5:02 am
The value in the table in the field is 08:10 for instance, however as its a NVARCHAR we cant subtract or use DateDiff on the values in To and From to get the time between the two values as they are nvarchars.
So, i thought i would need to convert the NVARCHAR to a DateTime and then i would be able to get the difference between the values. As the source data is crap i need to also add 1 minute to the TIMETO value.
Example:
TimeFrom: 07:55
TimeTo: 08:29
The difference between is 34 minutes, but the TimeTo should actually be 08:30 so the the total time for this "Visit" is 35 minutes.
The 35 minutes is what im trying to get to as an end result. Which if i use TOP 100 it works, and shows 00:35 (for 100 records), and thats what i need. I just cant do it for all records because i get that error.
Looked on MSDN and it shows that style 108 is the closest to what i needed, hh:mm:ss. Dont need seconds but its not held so they would all be 00 which is fine.
August 14, 2008 at 6:02 am
Any chance of having empty strings or other values not convertable to datetime?
_____________
Code for TallyGenerator
August 14, 2008 at 6:27 am
Try this
SELECTHC_IH_TD_ACTUAL_TIME_FROM,
HC_IH_TD_ACTUAL_TIME_TO,
CONVERT(CHAR(5), DATEADD(MINUTE, DATEDIFF(MINUTE, TimeFrom, TimeTo), '00:01'), 108) AS TimeDifference
FROM(
SELECTHC_IH_TD_ACTUAL_TIME_FROM,
CASE
WHEN HC_IH_TD_ACTUAL_TIME_FROM LIKE '[0-2][0-9]:[0-9][0-9]' THEN HC_IH_TD_ACTUAL_TIME_FROM
ESLE NULL
END AS TimeFrom,
HC_IH_TD_ACTUAL_TIME_TO,
CASE
WHEN HC_IH_TD_ACTUAL_TIME_TO LIKE '[0-2][0-9]:[0-9][0-9]' THEN HC_IH_TD_ACTUAL_TIME_TO
ESLE NULL
END AS TimeTo
FROMdbo.HC_IH_TIMESHEET_DETAIL
) AS d
N 56°04'39.16"
E 12°55'05.25"
August 14, 2008 at 6:48 am
Never thought about the NULL fields, good call!
As per the code above, excellent it works!!!! Thank you, did just over 811k rows in around 20 seconds 🙂
Thanks to you both for all your help.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply