How can I validate dates? 2

[Yerdon]

Hi there,

I am processing a table which has been populated over the years with invalid dates in the date fields - don't ask me how. Things like 9/31/2006 and stuff like that.

Does anybody know of a select statement that could return a list of the rows with bad dates?

Something like: select * from tablename where fieldname is BADDate()

Any ideas?

Thank you!

Joseph

 

[hneal98]

SELECT *
from table
where isdate(datecolumn) = 0

This will return every row that does NOT have a valid date.

Of course you change the "table" for your table name and "datecolumn" with the name of the field that contains the date.

I don't know what your complete challenge is, but you can change this to meet your needs.

http://www.totalsecurityforyou.com

for any personal security products.

 

[bborissov]

SELECT * FROM YourTable WHERE ISDATE(TheField) = 0

hat will work only if your field is n/var/char

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Yerdon]

Thanks to both of you. That is EXACTLY what I needed.