Using concat() in INTO OUTFILE gives me error code 1064

I'm trying to execute a query and export its results to a csv file with a formatted current_timestamp in its file name.

The query itself (excluding from INTO OUTFILE and onwards) executes just fine.
If I pass a simple string to INTO OUTFILE it exports to csv just fine i.e. making INTO OUTFILE 'C:\path\to\file.csv'.
If I execute the SELECT CONCAT(..) function and its parameters in another query window, I get a positive looking string returned so I know the concat function is fine, it produces C:\ProgramData\MySQL\MySQL Server 5.6\Uploads\2020-10-20_043918.csv.

I feel like INTO OUTFILE annoyingly doesn't expect anything other than a simple string, hopefully I'm wrong though. Does anyone have any insight to this?

SELECT
    COUNT(*) AS AgentCount,
    clients.ClientId,
    clients.Name AS ClientName,
    computers.RouterAddress
FROM
    computers
LEFT JOIN 
    clients
ON
    computers.ClientId = clients.ClientId
GROUP BY
    computers.RouterAddress
ORDER BY
    AgentCount DESC 
INTO OUTFILE 
    CONCAT('C:\\ProgramData\\MySQL\\MySQL Server 5.6\\Uploads\\', DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d_%H%i%s'), '.csv')
FIELDS TERMINATED BY 
    ','
ENCLOSED BY 
    '"'
LINES TERMINATED BY 
    '\r\n'

Solution

select ... into outfile does not support variables, as you are finding out. What you ask for requires dynamic SQL:

SET @sql = CONCAT_WS('\r\n',
    'SELECT',
    '   COUNT(*) AS AgentCount,',
    '   clients.ClientId,',
    '   clients.Name AS ClientName,',
    '   computers.RouterAddress',
    'FROM',
    '   computers',
    'LEFT JOIN',
    '   clients',
    'ON',
    '   computers.ClientId = clients.ClientId',
    'GROUP BY',
    '   computers.RouterAddress',
    'ORDER BY',
    '   AgentCount DESC',
    CONCAT('INTO OUTFILE \'', TRIM(BOTH '\'' FROM QUOTE(@@secure_file_priv)), DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d_%H%i%s'), '.csv\''),
    'FIELDS TERMINATED BY \',\'',
    'ENCLOSED BY \'\"\'',
    'LINES TERMINATED BY \'\\r\\n\'');

    PREPARE statement FROM @sql;

    EXECUTE statement;
    DEALLOCATE PREPARE statement;