Error when using DateAdd - TSQL

Question

I have an app that uses a table that has a varchar column [BadColumn] which is populated by a date in the format MM/DD/YYYY or at least that's what most of the rows contain.

I have no control on modifying this table and changing the data type.

I need to report on this data to show rows that are within a week.

Here's my problem:

every time I use something to compare/filter the date I get an error

Conversion failed when converting date and/or time from character string

So here's what I have tried so far :

Tried to create a view with the [BadColumn] being converted to a Date type column [NewBadColumn] and convert the value from the original table to a date type using Convert(date, BadColumn). The view gets populated, but now when I still try to query using DateADD or do any date comparisons I get the same error.
I have also tried to use Convert(Date, BadColumn, 105), but same problem.
I have tried other formats, but still the same issue cannot do a date comparison on that column.

I am not sure which row is throwing the error, how do I find it and fix this issue.

Thank you for the responses. I have tried using ISDate function to find the bad records. But I am still getting the same error "Conversion failed when converting date and/or time from character string." when I try to use DateAdd.

Here's the code I am using :

Select t.* from (Select * from dbo.BadTableName q with (nolock) where ISDate(BadColumn)=1 and ISDATE(BadColumn) Is Not Null ) t where t.BadColumn > DATEADD(dd,-2, GetDATE())

Any help is appreciated.

Look here stackoverflow.com/questions/3060952/…

Bobby
– Bobby

2014-06-16 15:12:02 +00:00
Commented Jun 16, 2014 at 15:12 — Bobby
– Bobby, Commented Jun 16, 2014 at 15:12

Narti · Accepted Answer · 2014-06-16 15:13:43Z

1

Try checking the column using the ISDATE() function. You can do this without creating a view first.

IF ISDATE(YourColumn) = 1
DATEADD (datepart , number , YourColumn )
ELSE 'No valid date provided'

answered Jun 16, 2014 at 15:13

Narti

1817 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RDs Over a year ago

I have used ISDate and failed, posted more info in the question

Narti Over a year ago

Have you had a look at the limitations of ISDATE() ? It Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0. ISDATE returns 0 if the expression is a datetime2 value. Dropping the second condition of your subselect should solve the issue.

Community · Accepted Answer · 2017-05-23 11:49:34Z

0

Look at this other stack over flow question and answer

Find invalid dates in SQL Server 2008

You can use the ISDATE function to test the individual rows.

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Jun 16, 2014 at 15:10

Bobby

2,9483 gold badges22 silver badges37 bronze badges

1 Comment

RDs Over a year ago

I have used ISDate and failed, posted more info in the question

Collectives™ on Stack Overflow

Error when using DateAdd - TSQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related