amberdextrous
MIS
Ok, I am pulling my hair out over this. I developed a VB program that has 3 drop-down lists: Agency/Client code, a start date & time, and an end date & time...the latter 2 are DateTimePickers. The user selects from these drop down lists and hits a button, which then passes the selected values to a stored procedure in SQL Server 2005. The SP then passes the results back, which are stored in a DataSet in VB and written to an XML file. The XML files display data for records that match the selected client code and that fall within the selected start and end date & time ranges.
My boss gave me a sample of the actual table to run it against, and when finished we'll deploy it against the real table. Everything worked fine when using the first sample table. However I recently exported the entire table (not just a sample) into our testing database, and found that out of 300,000 records there are about 10 that have invalid times in them. Most of the invalid ones have, for example, 0315 instead of 03:15:00, and 2 of them just have a single digit, such as 8, which I suppose is 08:00:00. These records weren't in the sample table. I have no idea how this happened, because the application that users run against the table auto-populates dates and times...it's as if a conversion didn't take place. Anyway, now my code will not work because of these invalid values in the Time column. Here is what it looks like...the SELECT clause is in reality a lot longer, but you get the idea...
Also, all columns are varchar...yes yes I know, date and time should be datetime, but I have been instructed not to change them.
I get this error now:
"Arithmetic overflow error converting expression to data type datetime."
I tried adding this in my WHERE clause right after the Client Code line:
But I still get the same error. I tried making a duplicate of the table and I went and fixed all the invalid times, and my code worked, so I know that's the problem. I could always fix the times in these columns, but then if the fields somehow get populated with invalid time data again, my program won't work.
Does anyone have any suggestions on how to handle this? At the very least, I need to be able to get my code to ignore the records with invalid times so that I can compare the valid ones with the parameters provided by the VB app. Or even better, cast the invalids (0315) to valid (03:15:00) and then run the query.
Though I suppose the best alternative would be to develop a SP that automatically fixes invalid times when new records are stored, which would in turn eliminate the problem I'm having. If anyone has any input on how to go about that as well, that would be fantastic.
My boss gave me a sample of the actual table to run it against, and when finished we'll deploy it against the real table. Everything worked fine when using the first sample table. However I recently exported the entire table (not just a sample) into our testing database, and found that out of 300,000 records there are about 10 that have invalid times in them. Most of the invalid ones have, for example, 0315 instead of 03:15:00, and 2 of them just have a single digit, such as 8, which I suppose is 08:00:00. These records weren't in the sample table. I have no idea how this happened, because the application that users run against the table auto-populates dates and times...it's as if a conversion didn't take place. Anyway, now my code will not work because of these invalid values in the Time column. Here is what it looks like...the SELECT clause is in reality a lot longer, but you get the idea...
Also, all columns are varchar...yes yes I know, date and time should be datetime, but I have been instructed not to change them.
Code:
SELECT
[Date of call] AS Date_of_call,
[Time of call] AS Time_of_call,
[Client Code] AS Client_Code
[Record ID] AS Record_ID
FROM [OSIRIS2 - Calls3]
WHERE
([Client Code] = CONVERT(nvarchar(255), @agencycode)) AND
(CONVERT(Datetime, [Date of call], 101)) + (CONVERT(Datetime, [Time of call], 108)) >= @startdate
AND (CONVERT(Datetime, [Date of call], 101)) + (CONVERT(Datetime, [Time of call], 108)) <= @enddate
ORDER BY
(CONVERT(Datetime, [Date of call], 101)) + (CONVERT(Datetime, [Time of call], 108))
I get this error now:
"Arithmetic overflow error converting expression to data type datetime."
I tried adding this in my WHERE clause right after the Client Code line:
Code:
AND (ISDATE([Time of call]) = 1 AND ISDATE([Date of call]) = 1)
But I still get the same error. I tried making a duplicate of the table and I went and fixed all the invalid times, and my code worked, so I know that's the problem. I could always fix the times in these columns, but then if the fields somehow get populated with invalid time data again, my program won't work.
Does anyone have any suggestions on how to handle this? At the very least, I need to be able to get my code to ignore the records with invalid times so that I can compare the valid ones with the parameters provided by the VB app. Or even better, cast the invalids (0315) to valid (03:15:00) and then run the query.
Though I suppose the best alternative would be to develop a SP that automatically fixes invalid times when new records are stored, which would in turn eliminate the problem I'm having. If anyone has any input on how to go about that as well, that would be fantastic.