A lot of the methods posed so far do the job but are actually relatively slow because of all the string manipulation using both concatenation and REPLACE. Using a nice general binary collation cuts about 30% of the time out but that's still relatively slow.
A pure math and a hybrid CONVERT with a couple of STUFFs results in a 50-55% improvement (twice as fast).
CONVERT, by itself, is nasty fast. As a bit of a sidebar, anything and everything is at least 7 times faster than (ugh!) FORMAT (FORMAT took 73 seconds on a 10 million row table). Don't use FORMAT for anything in SQL Server even for small stuff. It will become a part of the "Death by a Thousand Cuts" performance issues that most servers suffer.
If you really want performance to go along with accuracy and still be relatively simple, try the following. The added benefit is that the result can be stored in an 8 bit BIGINT instead of a 14 byte CHAR(14). For a 10 million row table, the other code posted took 10.4 seconds to run. The code below only takes about 3.6 seconds or about 3 times faster.
SELECT YYYYMMDDHHMISS = CONVERT(BIGINT,CONVERT(CHAR(8),GETDATE(),112))*1000000
If you really need the output to be character based and you're dumping it to a table, the the implicit conversion to a CHAR(14) column will only slow the code above down to about 3.9 seconds. If you want the nice warm fuzzies that avoiding implicit conversions provides, the following will do the trick with no addition time added.
SELECT YYYYMMDDHHMISS = CONVERT(CHAR(14),
Yeah... I know what a lot of folks are thinking. "Big deal! 3.6 seconds v.s. 10.4 on 10 MILLION rows! WHO CARES!!!????).
The answer is, everyone should... all the time. Just imagine if ALL your code ran almost 3 times faster with almost 3 times less CPU than the stuff you have running now. Heh... and if your stuff is in the cloud, thing of the money you might save not only on processors but on SQL Server licenses, as well.
"Make it work, make it fast, make it pretty... and it ain't done 'til it's pretty!"