IsDate() with DateTime2

Stef Merlijn
Stef Merlijn used Ask the Experts™
on
Hi,

After changing fieldtypes from DateTime to DateTime2 I get some error running specific SQL.

MyDateField might also be NULL so I need to check if the record contains a valid date:

     Select MyDateField FROM MyTable
     WHERE ((IsDate(MyDateField) = 1)

ERROR: Argument data type datetime2 is invalid for argument 1 of isdate function.

I did find this article, but there it doesn't say how to solve this.
http://www.databasejournal.com/features/mssql/article.php/3754961/SQL-Server-2008-Date-Functions-Part-2.htm

How can I correct this SQL so it will work again?
Are there any other issues when switching from DateTime to DateTime2?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You don't need to use ISDATE to check if the value is a valid date. The date is always valid (since it was already stored on the database on a date datatype field) unless the value is null, so you only need to test if the value is not null.

WHERE MyDateField IS NOT NULL
Stef MerlijnDeveloper

Author

Commented:
Can things be so easy...
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial