Conversion error with Varchar, datetime 1

[amberdextrous]

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.

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:

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.

 

[SQLSister]

Well this is one reason of the many why you should use datetime fields, but I understand you can't. Fix the existing bad data. Put a trigger on the table to ensure the data is a correct datetime before inserting it. This will cover inserts from any source not just the user interface.


"NOTHING is more important in a database than integrity." ESquared

 

[SQLBill]

How does the values get inputted in the first place? Is it via an application? If so, I would require that the application do the validation of the time so it passes it correctly.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[amberdextrous]

These solutions are what I was thinking needed to happen. It seemed like a problem that needed to be fixed on the user application side, but a trigger would also make a lot of sense. Yes, the values are inserted via an application. The user populates most of the fields in the table via the app, but the date and time are supposed to be auto-populated so that they are always in a consistent format. Somehow a few slipped by, however.

I am going to ask again about changing the fields to datetime. That seems like the best overall solution. If not, then I will see about a trigger and perhaps an app fix.

In the meantime, does anyone know how I could get my SP to effectively ignore records with invalid times so that the datetime conversion can still take place?

 

[gmmastros]

The problem you are having is with the where clause.

You see, you are TRYING to convert your varchar to datetime AND checking for IsDate all at the same time.

You see, you cannot guarantee the order in which SQL Server will evaluate the where clause expression. This, actually, is a good thing (most of the time). You see, SQL Server will attempt to reduce the number of rows as quickly as possible. Since you have AND in your where clause, if any one of the AND conditions evaluates to false, then the row is filtered out. With multiple AND conditions, some will be faster to evaluate than others. Take a look at this example:

[tt][blue]
Select 1
Where 1/0 = 0
[/blue][/tt]

This gives you an error: [red]Divide by zero error encountered.[/red]

Now, look at this...
[tt][blue]
Select 1
Where 1=1 or 1/0 = 0
[/blue][/tt]

Notice the 1/0. This should throw an error, but, because 1=1 is faster to evaluate than doing a division, there is no error.

There is, of course, a way to handle this. by being a little more creative with your where clause, (by using Case/When) in the converts.

Let me show you with an example. Suppose you had this data...

[tt][blue]2008-10-14
2007-10-14
Yesterday[/blue][/tt]

Clearly, the 'Yesterday' value is NOT a valid Date/Time. So, how do we deal with this?

First, let's create a query similar to yours, and see that we get the same error you are.

Declare @StartDate DateTime
Set @StartDate = '20081014'

Declare @Temp Table(DateCol VarChar(20))

Insert Into @Temp Values('20071014')
Insert Into @Temp Values('2008-10-14')
Insert Into @Temp Values('Yesterday')


Select	* 
From	@Temp 
Where	IsDate(DateCol) = 1
        And Convert(DateTime, DateCol, 101) >= @StartDate

We get: [red]Conversion failed when converting datetime from character string.[/red]

It seems to me that CONVERT must be a less expensive (think faster) operation than IsDate, so the convert is evaluated first.

Here's an example that works the way you would expect it to.

Declare @StartDate DateTime
Set @StartDate = '20081014'

Declare @Temp Table(DateCol VarChar(20))

Insert Into @Temp Values('20071014')
Insert Into @Temp Values('2008-10-14')
Insert Into @Temp Values('Yesterday')

Select	* 
From	@Temp 
Where	Convert(DateTime, Case When IsDate(DateCol) = 1 Then DateCol Else 'Jan 1, 1900' End, 101) >= @StartDate

You see, we use Case/When to check for a valid date. If the date is valid, we use it. If it's not valid, we use Jan 1, 1900 instead.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

George you are awsome, love your explanations!!!!

 

[amberdextrous]

Pure genius! This does indeed work! The only difference is I chose to use NULL in the ELSE statement, rather than a value. Then I know there's no possible way the invalid data could be returned to the user with inaccurate dates/times. Seeing as how this company wasn't even around in 1900, I don't think anyone would be selecting that timerange, but you never know when it comes to users poking around with stuff

When I talked to my boss earlier, I was saying that even in the event I could get it to ignore invalid records, there's still the problem of missing data. He said in order to deal with the fact that data could end up missing from user-generated XML files due to invalid times, later down the road we can create a report functionality for them so they can get a daily report of what data was stored the previous day, and if they notice errors they can correct it themselves via the app (which will update it in the database). Their dept. has an administrator that can change the data, and then once it's changed, it should be able to be picked up by the SP. Apparently we (IS) like to stay out of that dept.'s data and let their administrator handle it.

Thank you George!

 

[gmmastros]

Apparently we (IS) like to stay out of that dept.'s data and let their administrator handle it.

That's a smart move.

Using NULL is a good way to go, but....

Apparently, you have 2 columns, one for Date and another for time. Of course, this does complicate matters. The reason I used Jan 1, 1900 in my example was to accomodate the time. Let me explain.

First, if the date is invalid, then it makes sense to toss the whole row (filter it out). However, if the date is valid and the time is not, you may want to return the row anyway. If you use Jan 1, 1900, a time of mid-night is implied. Many people don't know this, but you can actually add 2 DateTime values. Often times, it's weird to do this, but in your situation, probably makes the most sense.

You see, if you have a date in one column, and a time in the other, you can add them together to get a DateTime.

Let me show you with some sample data...

Declare @Temp Table(DateCol VarChar(20), TimeCol VarChar(20))

Insert Into @Temp Values('2007-10-14', '10:20 AM')
Insert Into @Temp Values('2008-10-14', 'Before Lunch')
Insert Into @Temp Values('Yesterday', '8:30 PM')

Select DateCol,
       TimeCol,
       Case When IsDate(DateCol) = 1 Then DateCol Else NULL End,
       Case When IsDate(TimeCol) = 1 Then TimeCol Else 'Jan 1, 1900' End
From   @Temp

Select DateCol,
       TimeCol,
       Convert(DateTime, Case When IsDate(DateCol) = 1 Then DateCol Else NULL End),
       Convert(DateTime, Case When IsDate(TimeCol) = 1 Then TimeCol Else 'Jan 1, 1900' End)
From   @Temp

[green]
-- Invalid dates are null
-- Invalid times are treated as midnight[/green]
Select DateCol,
       TimeCol,
       Convert(DateTime, Case When IsDate(DateCol) = 1 Then DateCol Else NULL End)
       + Convert(DateTime, Case When IsDate(TimeCol) = 1 Then TimeCol Else 'Jan 1, 1900' End)
From   @Temp

As you can see, if the date is invalid, you get null. If the time is invalid, you get the date at midnight. Depending on your query, you may still want to show the rows with an invalid time.

Obviously, invalid data should be fixed as soon as possible. The only reason I am showing this is because it gives you another option for selecting your data.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[amberdextrous]

Ah, that does make sense. That may a better option. Thanks for all your help!