Why does this conversion to date fail on some rows in my table and not other rows when I use an IIF

Question

I have this table and data:

CREATE TABLE dbo.tBadDate
(
  BadDateID    int NOT NULL,
  StartDate    nchar(20) NULL,
  CONSTRAINT [PK_tBadDate] PRIMARY KEY CLUSTERED 
  (
    [BadDateID] ASC
  )
);

INSERT dbo.tBadDate (BadDateID, StartDate) VALUES 
(1, N'1/1/2020            '),
(2, N'Jan 1 2021          '),
(3, N'January 1 2021      '),
(4, N'Ja 1 2021           '),
(5, N'Jan,1,2021          '),
(6, N'2021.1.1            '),
(7, N'8/8/1981            '),
(8, NULL),
(9, N'January First, 2021 ');

This script works:

SELECT StartDate, ISDATE(StartDate) from tBadDate;

This script fails:

SELECT StartDate, IIF(ISDATE(StartDate) = 1 , CONVERT(DATE, 
  startDate), 'Undefined Format') 
  FROM tBadDate

Msg 241, Level 16, State 1
Conversion failed when converting date and/or time from character string.

Your IIF returns a varchar or a date. Since a column can only have one datatype, the varchar value `'Undefined Format'` is attempted to be converted to a date. Even so, those different formats are unlikely to be converted with a single `CONVERT`, you'll need different styles for the different formats. — HoneyBadger, Nov 10 '21 at 15:25
Why are you storing date (and time) values as a `varchar` in the first place, and why are they different formats every single time? ***There*** is your problem. Fix your design; there are 6 date and time data types, and 5 of them are infinitely a better choice of data type. — Thom A, Nov 10 '21 at 15:27
There is *no* season for "Store Date (and time) data in a `varchar`" @nicomp; it's just wrong. — Thom A, Nov 10 '21 at 15:33
@HoneyBadger If I change the script to SELECT StartDate, IIF(ISDATE(StartDate) = 1 , CONVERT(DATE, startDate), CONVERT(DATE,'1/1/1900')) FROM tBadDate I still get the same error. — nicomp, Nov 10 '21 at 15:34
@nicomp `ISDATE` (and `ISNUMERIC`) aren't great functions; both can give false positives/negatives as they check if the value can be converted to *at least* one of the relevant data types (but that doesn't mean it can be converted to the one you want to convert the value to). `TRY_CONVERT` and `TRY_CAST` are *significantly* better choices. But, again, the BEST™ solution is: Fix your design. — Thom A, Nov 10 '21 at 15:35
@AaronBertrand I have the option to rollback edits. Thank you. — nicomp, Nov 10 '21 at 20:27
*" I have the option to rollback edits."* That doesn't mean you should. Stop adding noise to the question and asking people to not downvote you; it's actually more likely you *will* receive downvotes because of them... — Thom A, Nov 10 '21 at 20:52
Mod here - don't roll back the edits, @nicomp. [You don't have an applicable use-case for images](https://meta.stackoverflow.com/a/285557/6296561) over text. Either add it as text, or leave it out. Independently of that, noise like "Don't downvote me" don't belong in your post at all, and generally only gets you _more_ downvotes, because psychology or whatever. I've locked the post for an hour - don't roll back when it unlocks. — Zoe, Nov 10 '21 at 21:04

score 2 · Accepted Answer · answered Nov 10 '21 at 15:26

2

You could use try_convert with a cross apply

select StartDate,
    Iif(x.v is null,0,1) ValidDate,
    IsNull(Cast(v as varchar(20)),'Undefined Format')
from tBadDate
cross apply (values(Try_Convert(date,StartDate)))x(v)

answered Nov 10 '21 at 15:26

Stu

30,392
6
14
33

Why does this conversion to date fail on some rows in my table and not other rows when I use an IIF

1 Answers1