There are several functions available in T-SQL under the [Conversion][1] category, all of which have variants prefixed with `TRY_` which:

> Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.


* [`CAST`][2] / [`TRY_CAST`][4]
* [`CONVERT`][2] / [`TRY_CONVERT`][5]
* [`PARSE`][3] / [`TRY_PARSE`][6]

You can use them like this:

```sql
SELECT TRY_CAST('2020-05-27' AS DATE)
SELECT TRY_CONVERT(DATE, '2020-05-27')
SELECT TRY_PARSE('2020-05-27' AS DATE)
```



[1]: https://learn.microsoft.com/en-us/sql/t-sql/functions/conversion-functions-transact-sql
[2]: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
[3]: https://learn.microsoft.com/en-us/sql/t-sql/functions/parse-transact-sql?view=sql-server-ver15
[4]: https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver15
[5]: https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-ver15
[6]: https://learn.microsoft.com/en-us/sql/t-sql/functions/try-parse-transact-sql?view=sql-server-ver15

I have the following code in a stored procedure in MSSQL Transact:

    CAST(CONVERT(Datetime, aof.Transactiondate) AS date)

aof.Transactiondate is a varchar(8) and should be written in the form of '20160202' or '20160117'.
Today I found out that in rare instances aof.Transactiondate can be a '1' and thus my code crashes completely.

Can I do anything to make sure that the above don't crash and sets a defult date or something instead of crashing??

Theoretically I could make sure that there is 8 chars in the varchar and then seperate the char into 4, 2 and 2 block and finally make sure that block one is between 2015 and 2016 (only relevant at the moment), second block is between 1 and 12 and third block is between 1 and 31 but that seems to be a huge amount of work.

Safe convert from string to date in Transact

Answer