CODEWITHSUNDEEP
sqlsql-servert-sql
jezz
jezz

Reputation: 3

Validate, filter and convert string to date using Isdate

Hi I am having a problem converting string to date using a isdate for validation and here's my script

USE SomeDB
    SELECT  InstallDate
    FROM         tblInstalls
    WHERE     (ISDATE(InstallDate) = 0) AND (InstallDate IS NOT NULL)

--Result---
27/05/2002
27/08/2004
19/06/2002
26/06/2002
17/05/2002
15/09/2002
15/04/2004
19/04/2004
22/04/2004
22/04/2004
22/06/904

And now I would like to convert which can be converted to 103 format and ignore the data which cannot be converted...

USE SomeDB
SELECT  top 100   convert(DATETIME, InstallDate,  103))
FROM         tblInstalls
WHERE     (ISDATE(InstallDate) = 0) AND (InstallDate IS NOT NULL)


---Result--
2002-05-27 00:00:00.000
2004-08-27 00:00:00.000
2002-06-19 00:00:00.000
2002-06-26 00:00:00.000
2002-05-17 00:00:00.000
2002-09-15 00:00:00.000
2004-04-15 00:00:00.000
2004-04-19 00:00:00.000
2004-04-22 00:00:00.000
2004-04-22 00:00:00.000
22/06/904 <-The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

My objective is to clean a data in the right date format and remove the unnecessary data which cannot be converted. Hoping and appreciated you help in advance..

Upvotes: 0

Views: 1259

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

This is because you can't determine which order the engine will process. It may do the conversion of the row before or after it has filtered them.

As a side note, you have top but no order by. That will return whatever rows the sql engine wants to and it won't always be consistent. Secondly the additional where predicate of InstallDate IS NOT NULL is just wasted effort because you are already filtering only rows that have ISDATE = 1 and NULL is not a valid date.

with ValidDates as
(
    select top 100 InstallDate
    from tblInstalls
    where ISDATE(InstallDate) = 1
    --need an order by here
)

select convert(DATETIME, InstallDate,  103)
from ValidDates

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

First, IsDate returns 0 if the string value it gets is not a valid date.
you need to filter by IsDate(InstallDate) = 1.
Second, you probably get 0 as the result of IsDate since the default date format of your server is not dmy.

Here is something that will work:

set dateformat dmy; -- NOTE THIS ROW!

select convert(datetime, InstallDate,  103)
from tblInstalls
where isdate(InstallDate) = 1 -- NOTE: IsDate returns 1 for valid dates

see sql fiddle here.

btw, sean had some really good points in his answer, he only got the reason for the error wrong. AFAIK, the select clause will always be performed after the where clause.

Upvotes: 3

Related Questions