CODEWITHSUNDEEP
sql-servert-sql
Abdul Rasheed
Abdul Rasheed

Reputation: 6719

ISDATE producing error for DATE (datatype) value

I have a MyDate(datatype DATE) column in My_Table

MyDate
2016-11-01
0001-01-01
2016-08-01

When i execute the following query

SELECT  ISDATE(MyDate),* FROM My_Table

I am getting the following error

Msg 8116, Level 16, State 1, Line 2 Argument data type date is invalid for argument 1 of isdate function.

I thought the issue may the date '0001-01-01'. But when i execute 

SELECT ISDATE('0001-01-01')

It is working as expected and produced the result 0.(ie not a date)

But

DECLARE @V AS DATE = '0001-01-01' --no error
SELECT ISDATE(@V) --error

This is producing the same error. What i am wrong here?

Edit:- If i remove the values '0001-01-01' from Mytable, the query SELECT ISDATE(MyDate) is working properly without any datatype conversion.

Upvotes: 1

Views: 5212

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82474

Please note that Sarthak Grover's answer covers exactly the reason why you get a syntax error. I only posted my answer to elaborate a bit and to add an explanation on the reason why isdate('0001-01-01') returns false, when it's a perfectly valid value for the Date data type.

The entire point of the ISDATE function is to determine if the argument can be converted to a valid Date, time or DateTime, under the current DATEFORMAT settings, simply by using cast (as apposed to convert, that accepts a style parameter thus does not depend on the current date format settings.
Passing an argument of type DATE to the ISDATE function is nonsensical, since if the type of the argument is DATE, there is no need to check it.

Also, it is explicitly forbidden to pass an argument of date and time data types, except datetime and smalldatetime, as Sarthak Grover pointed out in his answer (+1 from me, btw).

the ISDATE function will return false even if the argument is convertible to a date value, if it's representing a date value before 1753-01-01, or after 9999-12-31.
This is because the value range of the DATETIME data type is from 1753-01-01 00:00:00 to 9999-12-31 23:59:59.997.

Here is a simple demonstration (try it yourself on rextester):

DECLARE @T AS TABLE
(
    DateString varchar(20),
    Comments varchar(100)
);

INSERT INTO @T (DateString, Comments) VALUES
('1752-12-31', 'will always return false'),
('1753-01-01', 'will always return true'),
('2018-02-27', 'depends on dateformat'),
('2018-27-02', 'depends on dateformat');

SET DATEFORMAT YMD;

SELECT  DateString, 
        Comments, 
        ISDATE(DateString) As IsDate
FROM @T;

SET DATEFORMAT YDM;

SELECT  DateString, 
        Comments, 
        ISDATE(DateString) As IsDate
FROM @T;

Results:

DateString      Comments                    IsDate
1752-12-31      will always return false    0
1753-01-01      will always return true     1
2018-02-27      depends on dateformat       1
2018-27-02      depends on dateformat       0

DateString      Comments                    IsDate
1752-12-31      will always return false    0
1753-01-01      will always return true     1
2018-02-27      depends on dateformat       0
2018-27-02      depends on dateformat       1

Upvotes: 2

Sarthak Grover
Sarthak Grover

Reputation: 121

ISDATE ( expression )

Arguments

expression Is a character string or expression that can be converted to a character string. The expression must be less than 4,000 characters. Date and time data types, except datetime and smalldatetime, are not allowed as the argument for ISDATE.

Source : https://learn.microsoft.com/en-us/sql/t-sql/functions/isdate-transact-sql

Upvotes: 3

Ajay
Ajay

Reputation: 774

ISDATE() can only evaluates STRING so try like this.

DECLARE @V DATE = '0001-01-01'
SELECT ISDATE(CAST(@V AS VARCHAR(30)))

Upvotes: 3

Related Questions