Does this temporary option affect only the current connection or the whole database?

Yes, note the docs:

When the SET TEMPORARY OPTION statement is not qualified with a user ID, the new option value is in effect only for the current connection.

As stated in the answer, you might as well adapt the date_format and timestamp_format options temporarily, so you can convert according strings into date data types without the need do use the dateformat function. That might be "more elegant" particularly when you have to treat several different date values.

And of course you do not need a separate variable @birthday within the UPDATE statement, you can simply code "update employee set birthday = dateformat(...)"

Ah, sorry, I misread your sample. Your usage of the DATEFORMAT function is somewhat unusual: It expects a date/datetime as first parameter, whereas you use it with a string that has to be automatically converted to a date beforehand.

So your sample function calls basically

1. convert from string to date values (which requires fitting date order, otherwise it will raise a conversion error, as mentioned in my answer)
2. and then back to the desired string format, which is independent of date order. My previous comment was simply dealing with that step.

In other words:

select dateformat(current date, 'yyyy-mm-dd'), dateformat(current date, 'dd.mm.yyyy'), dateformat(current date, 'mm/dd/yyyy')

should work independen to the mentioned options, as they deal with converting string to date values whereas the DATEFORMAT() function does the opposite.

The approach to import external data with varying or unfitting format into a local temporary table with a VARCHAR data type instead of a number or date data type and then to insert data from the temporary table into the permantent table with according data conversion is certainly very common. We do use that as well, say when importing numerical values formatted with comma as decimal separator or with currency symbols. For such cases, we would then typically use both a "source-value-as-is" VARCHAR column and a "target-datatype" column in the temporary table.

Assuming you would have both a "BirthdayString" VARCHAR column and a "BirthdayDate" DATE column within your TempEmployee table.

If there are just those two date formats (dd.mm.yyyy and yyyy.mm.dd), you might as well use one single update statement with the YMD function and without the need to adapt the date_order, such as

update TempEmployee
set BirthdayDate =
   if charindex('.', BirthdayString) = 3
   then -- 'dd.mm.yyyy'
      ymd(substring(BirthdayString, 7, 4),
          substring(BirthdayString, 4, 2),
          substring(BirthdayString, 1, 2))
   else -- 'yyyy.mm.dd'
      ymd(substring(BirthdayString, 1, 4),
          substring(BirthdayString, 6, 2),
          substring(BirthdayString, 9, 2))
   end if
where BirthdayDate is null;

Not necessarily better, just another approach.

Well, we usually have to insert or update rows from external data into permanent tables, so it feels easier to correct/normalize the external data within the temp tables before updating permanent tables - so in case the conversion fails those errors happen before permanent tables are touched at all. Just a preference, there's nothing wrong with your approach AFAICT...