Select IDs of lines where nvarchar column cannot convert into date

by

Let’s make it super simple: 2 columns, id and textDate.

For some of the lines, the textDate field will contains null, which is ok, sometimes it will contains something like ‘2017-06-24’ which is also ok.

But for some of the lines (and this should’ve been validated before it got to this point) the field will contains ‘A value placed there to complicate things’.

I want to know which line aren’t null and does not follow the format ‘yyyy-mm-dd’.

I searched but it’s so specific that my search didn’t lead anywhere to what I needed.

  • 2

    check out TRY_CAST learn.microsoft.com/en-us/sql/t-sql/functions/…

    – 

  • 2

    …and storing dates using, I don’t know, the date data type. That’s what it’s for, and prevents you from having to validate anything. 🙂

    – 

  • 1

    Select * from YourTable where try_convert(date,textDate) is null and textDate is not null

    – 

  • 1

    If you want to ensure that the date is in one particular format, yyyy-mm-dd as opposed to either dd/mm/yyyy or mm/dd/yyyy, then you should include a date and time style code in your call to try_convert, e.g.: try_convert(date, textDate, 23) is null -- find invalid values Ref: Date and time styles

    – 




Leave a Comment