ISDATE function not working as expected

Question

ISDATE function not working as expected

squvi.87

Default port

Points: 1450
More actions
September 24, 2015 at 1:40 am

#300974

Declare @var varchar(100)
set @var = '2000'
select case when ISDATE(@var ) =1 then 'Valid Date' when ISDATE(@var ) =0 then 'Invalid Date' END
In the above code I have given value for @var is 2000, which is not date. but if I check with the isdate function it returns as it is a valid date.
what we could do to get the expected output?
Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

squvi.87 (9/24/2015)
Declare @var varchar(100) set @var = '2000'
select case when ISDATE(@var ) =1 then 'Valid Date' when ISDATE(@var ) =0 then 'Invalid Date' END
In the above code I have given value for @var is 2000, which is not date. but if I check with the isdate function it returns as it is a valid date.
what we could do to get the expected output?
Thanks in advance

It's confusing isn't it, even when you read the BOL entry. However, if you interpret ISDATE() as meaning "can be converted to a date, time or datetime" then it makes better sense:

Declare @var varchar(100)

set @var = '2000'

select case when ISDATE(@var ) =1 then 'Valid Date' when ISDATE(@var ) =0 then 'Invalid Date' END

SELECT CAST(@var AS DATE)

set @var = '200'

select case when ISDATE(@var ) =1 then 'Valid Date' when ISDATE(@var ) =0 then 'Invalid Date' END

SELECT CAST(@var AS DATE)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 2

squvi.87 (9/24/2015)
what we could do to get the expected output?

Assuming that your string date is in 'yyyymmdd' format then you could do:

SELECTTestDate,

ISDATE(TestDate) AS [IsDate?],

CASE WHEN TestDate LIKE '[12][0-9][0-9][0-9][01][0-9][0-3][0-9]'

THEN 'RegExOK' ELSE 'RegExBad' END,

CASE WHEN TestDate LIKE '[12][0-9][0-9][0-9][01][0-9][0-3][0-9]'

AND IsDate(TestDate) = 1

THEN 'Valid' ELSE 'Invalid' END

FROM

(

SELECT'2000' AS [TestDate]-- Valid year of IsDate() but fails RegEx

UNION ALL SELECT '20001231'-- Valid

UNION ALL SELECT '20000231'-- Passes reges, Feb. does not have 31 days!

UNION ALL SELECT '20009999'-- Fails regex

) AS X

TestDate IsDate?

-------- ----------- -------- -------

2000 1 RegExBad Invalid

20001231 1 RegExOK Valid

20000231 0 RegExOK Invalid

20009999 0 RegExBad Invalid