SQL Server Date Conversion Error

Question

I have a large table with 1 million + records. Unfortunately, the person who created the table decided to put the dates in the field varchar(50).

I need to do a simple date comparison -

datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31

But it does not work on convert():

Conversion failed when converting datetime from character string.

Apparently, there is something in this field that he does not like, and since there are so many entries in it, I can’t say just by looking at it. How can I correctly deactivate the entire date field so that it does not interrupt on convert()? Here is what I have now:

select count(*)
from MyTable
where
    isdate(lastUpdate) > 0
    and datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31

---

@SQLMenace

In this case, performance does not concern me. This will be a one-time request. Changing the table in the date and time field is not an option.

@Jon Limjap

I tried to add a third argument, and that doesn't make any difference.

---

@SQLMenace

, , , , ; ISO YYYYMMDD; ISO 8601 yyyy-mm-dd Thh: mm: ss: mmm ( )

isdate() ?

100%. 30 .

---

@SQLMenace

select isdate('20080131') -- returns 1
select isdate('01312008') -- returns 0

---

@Brian Schkerke

CASE ISDATE CONVERT().

! .

Answer 1

CASE ISDATE CONVERT().

SELECT COUNT(*) FROM MyTable WHERE DATEDIFF(dd, CONVERT(DATETIME, CASE IsDate(lastUpdate) when 1 then lastUpdate ELSE '12-30-1899' end), getDate()) < 31

'12-30-1899' .

Answer 2

, ? . , .

, 3 1 , .

select * into BadDates
from Yourtable
where isdate(lastUpdate) = 0

select * into GoodDates
from Yourtable
where isdate(lastUpdate) = 1

BadDates ,

Answer 3

ISDATE() , , . , , , , , DATEDIFF - .

SQL Server Management Studio, CTRL + L, .

, SQL , , , .

Answer 4

, , varchar, : CAST CONVERT (T-SQL)

Answer 5

, ?

.

, .

- , ISDATE(). , . +1 SQLMenace.

Answer 6

datetime, ,

WHERE datediff(dd, convert(datetime, lastUpdate), getDate()) < 31

, , datetime colum, n

where lastUpdate > getDate() -31

Answer 7

. , varchar (50) .

.

Answer 8

, ,

ISO YYYYMMDD

ISO 8601 yyyy-mm-dd Thh: mm: ss: mmm ( )

.

SET DATEFORMAT YMD ( , ),

Answer 9

Would the isdate () check take care of this?

Run this to see what happens.

select isdate('20080131')
select isdate('01312008')

Answer 10

I am sure that changing the table / column may not be due to any outdated system requirements, but you thought about creating a view that integrates date conversion logic, if you are using a newer version of sql, then you can even use the indexed view ?