I am attempting to understand how TRY_PARSE actually works under the hood. I've read through the Documentation for TRY_PARSE by Microsoft. The documentation makes sense until I run some of my own tests. Using 2022-01-26T12:00:00.000Z and #2022-01-26T12:00:00.000Z# I will get a valid DateTime returned from the TRY_PARSE function; however, using !2022-01-26T12:00:00.000Z! I will get null returned from the TRY_PARSE function.

What special characters are allowed to wrap a date? Why does the # work but not !?

CodePudding user response：

The TRY_PARSE function uses the .NET CLR to parse the values, so the rules for TRY_PARSE(@s As datetime) are the same as for .NET's DateTime.TryParse method:

DateTime.Parse Method (System) | Microsoft Docs
Any leading, inner, or trailing white space character in s is ignored. The date and time can be bracketed with a pair of leading and trailing NUMBER SIGN characters ("#", U 0023), and can be trailed with one or more NULL characters (U 0000).

If your string uses any "special" character other than # around the date, it will not parse.

Also, if you have a mis-matched # at the start or the end of your date, it will not parse.