[SQL Server] Simple ISDATE Test

[SQL Server] Simple ISDATE Test

ISDATE System function returns 1(true) when the expression is a valid date(datetime) type.

Script

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT ISDATE('20190101') AS '1', ISDATE('2019/01/01') AS '2', ISDATE('2019-01-01') AS '3', ISDATE('19-01-01') AS '4', ISDATE('2019-04-31') AS '5', ISDATE(NULL) AS '6', ISDATE('201l-12-31') AS '7', ISDATE('123') AS '8', ISDATE('2019-01-01 15:55:59.455') AS '9', ISDATE('2019-01-01 15:55:59.4551') AS '10', ISDATE('1752-12-31') AS '11', ISDATE('1753-01-01') AS '12', ISDATE('2999-01-01') AS '13', ISDATE('9999-12-31') AS '14', ISDATE('01/2019/12') AS '15';

Result

Description

1. Standard ISO Type. Returns "1".
2. Standard JAPAN type. Returns "1"
3. Standard ISO8601 (Also ODBC canonical) type. Returns "1"
4. Cannot recognize "19" as YEAR. Returns "0"
5. April 31st does not exist. Returns "0"
6. NULL does not regard as a date type. Returns "0"
7. There's "l", not "1". Returns "0"
8. It's a numeric type. Returns "0"
9. Standard ODBC canonical (with milliseconds) type. Returns "1"
10. Look like Standard ODBC canonical (with milliseconds) type. But it's not because of the additional "1". Returns "0"
11. This is because SQL Server Date type uses the Gregorian calendar and originally from Sysbase Database. SQL Server cannot recognize the date before 1753. Returns "0"
12. From January 1st, 1753, there's no problem to recognize to a date type. Returns "1"
13. Standard ISO8601 type. Returns "1"
14. Standard ISO8601 type. Returns "1"
15. Recognizes as January 12th, 2019. Returns "1"

Reference

• CAST and CONVERT (Transact-SQL) - Date and Time styles: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#date-and-time-styles
• ISDATE (Transact-SQL): https://docs.microsoft.com/en-us/sql/t-sql/functions/isdate-transact-sql?view=sql-server-ver15