Nested NULLIF with ISDATE to eliminate records

Question

I have a column (datatype nvarchar(max), not my choice, legacy) with various different uses to the end user dependent on other factors.

I was trying to narrow down to certain specific data within that column given earlier clauses within some sample (but highly representative) data I noticed that the remaining data was either a 1,2,3,4, or a date

I initially added some nested NULLIF along with an IS NOT NULL

AND NULLIF(NULLIF(NULLIF(NULLIF([ColumnName],'1'),'2'),'3'),'4') IS NOT NULL

The 1 is as a string because in the global data there are some strings so removing the single brackets creates an implied conversion to int which many records would fail. This got me down to 25 records in my sample, the records with dates and the target records

I then thought I'd add an ISDATE to isolate those records

AND NULLIF(NULLIF(NULLIF(NULLIF(ISDATE([ColumnName]),'1'),'2'),'3'),'4') IS NOT NULL

This then returned 60k or so records, which was not the behaviour I expected.

I ran the following queries to see if there was any incompatibility with the two commands inline but the returned as expected

SELECT NULLIF(ISDATE('06/01/2022'),1)

returned NULL.

SELECT NULLIF(ISDATE('06/01/2022'),'1')

in case it didn't like a string, but returned NULL.

SELECT NULLIF(NULLIF(ISDATE('06/01/2022'),'1'),'2')

in case it didn't like the nest, but returned NULL.

So why does it not NULL the values that present as dates, and also why does it negate the other NULLIF commands in the outer parts of the nest?

Answer 1

Turns out I'm an idiot

I forgot to catch all the times that the ISDATE() returned a 0, so it was returning all the values that the NULLIFs were trying to catch

AND ISDATE(ISNULL(NULLIF(NULLIF(NULLIF(NULLIF([2nd Ref],'1'),'2'),'3'),'4'),'06/01/2022')) = 0

So changing the order here helped, creating NULLs for the expected values, changing those into a known date, then returning where it isn't a date

That should now work as expected. Oh well, someone might one day find it useful

edit: There are a couple of records that still have dates in them, I will also use the TRYCONVERT as Jeroen suggested