I'm new to SQL and I'm absolutely astounded by how difficult it has been to find some proper resources on error handling. Every tutorial I see is declaring variables and handling errors that arise from using those variables improperly. Who is out there using SQL as a glorified calculator?
I have read-only access to a database which I use exclusively for reporting, and want to see records which have one of three date entries in a certain range. Two of the columns are datetime, but the third is varchar(10). As I understand it, I need to convert varchar to datetime to do any sort of date comparison, so I have this:
SELECT id, date1, date2, date3
WHERE date1 >= Convert(datetime, '2020-05-01')
OR date2 BETWEEN '2020-05-01' AND '2020-06-01'
OR date3 BETWEEN '2020-05-01' AND '2020-06-01';
But I hit a predictable error: an entry of date1 was input as 8/301/2017 and can't be converted to datetime. I read that SQL has TRY / CATCH blocks and enclose my query in one, but I don't get any further because once I hit an error I'm shifting to a whole new query. I tried putting the TRY / CATCH block inside the query a few different ways, but none of them made for valid code. CONTINUE looks right but can't be used without WHILE, and I'll be *extremely* disappointed if I have to manually tell SQL to process each row just because of one bad egg, or create a subquery to fetch the back half of the table. It seems to me this should be the most basic operation... I just want SQL to calm down and breath when it hits the 8/301 row, pretend that value is NULL or 1/1/1900 or something, and move on with its life. What am I missing?
[`TRY_CONVERT`](https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-ver15) is what you are after:
> Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
So it will "pretend that value is NULL" as you say.