0

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?

cockbeard
  • 11
  • 3
  • `ISDATE` is a bad function you should not use, as it offers no way to specify the formats you want to accept (and not all date types accept all formats). Use `TRY_CONVERT`/`TRY_PARSE` instead. Both return `NULL` on failure, which is easy to combine with `ISNULL`/`COALESCE`. – Jeroen Mostert Mar 03 '22 at 18:03
  • For the test I'd suggest `([Column] IN ('1','2','3','4') OR TRY_CONVERT(DATE, [Column]) IS NOT NULL)` (add third parameter to control the format as necessary) rather than chaining `NULLIF`. – Jeroen Mostert Mar 03 '22 at 18:06
  • Thanks Jeroen, I'll look into that as some slipped through the net, that's why I deleted my answer in the hope that someone else might make better more elegant suggestions – cockbeard Mar 03 '22 at 18:24
  • `ISDATE` asks if a string is a date and only returns 1 or 0, so you'd want it around your `NULLIF` not inside it. And it's a terrible function, you should probably use `TRY_CONVERT` as mentioned – Charlieface Mar 03 '22 at 20:07

1 Answers1

0

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

cockbeard
  • 11
  • 3