Did you look at the first article I suggested? Please post what you've tried so far.

Basically it means that you probably have a corrupted record in there somewhere that the app creator didn't anticipate. It is probably expecting 1:1 relationships and you have a...

Man, I'm dropping the ball all over this thread! I made this post about 5 hours ago and clicked preview instead of post. Oops.

tacy.highland (10/16/2009)

---

I think I figured...

I may not check this again for a while after you respond (SQL Saturday tomorrow!), so my theory is that you have a hidden character (likely char(10), char(13) or char(9)...

And to follow that up, if it does not fail, try this case instead:

CASE WHEN ISDATE(Action) = 1 THEN Action

WHEN Action IN ('Y','YES') THEN GETDATE()

ELSE '1/1/1900'

END

I know you said you're not new so don't take this the wrong way, but it happens to the best of us so I have to doublecheck:

Have you tried ditching...

The Patindex should only return the values which do NOT comply.

I have a new theory. Run the following, post the results.

SELECT COUNT(*) FROM CounterData

WHERE PATINDEX('[12][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9]%[012][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]',CounterDateTime) = 0

SELECT COUNT(*) FROM...

tacy.highland (10/16/2009)

---

I'm open to hearing whatever you got.

As far as the temp table goes, I technically could've used joins but it was much simpler for me to use the view...

Wow, that's terrible. I guess I'm a bit distracted today, I forgot to actually post the code at the end! :blush:.

Here is what I was alluding to in my...

Try these:

SELECT * FROM CounterData

WHERE ISDATE(CounterDateTime) = 0

SELECT * FROM CounterData

WHERE PATINDEX('[12][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9] [012][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]',CounterDateTime) = 0

If there is any value in your table (even if you're excluding it with a where clause) that cannot be converted to a datetime, it will fail.

It should give you...

Deletion of values is not the same thing as deletion of columns. This may help explain it, it may not. Don't run these, they're just to help explain.

You...

It's ok, we'll start with this. I'm not quite sure what values you need to pull, but I'm throwing this out there to see if it would make it...

I'm actually surprised this question isn't asked more.

Deleting the entire column is a whole new ball of wax. That isn't data in the table changing, that is the table itself changing. To the best of...