I have a table function that needs to strip some text from a string and see if the remaining string is a valid date. (Don't ask... crappy database design and application over which I have no control!)
The problem is that we are in the UK and the database/instance has been set up to default to MDY (US format) so when I run isDate('30/09/2022') I get 0 :(. It works if I do
SET DATEFORMAT DMY
But when I put the SET DATEFORMAT DMY into the table function I get the error message
Invalid use of a side-effecting operator 'SET COMMAND' within a function
I can strip the date down into its parts and recompile it, but I wondered if there was a more elegant solution. If not I will likely throw it into a utility function so that the parser can be more flexible.