How to use sqlcmd to create file names for output based on the day of the week or the date? I have a SQL agent job that queries the database and exports the results into a csv file.
It needs to run daily and The output file (Output.csv) gets overwritten everytime the job runs. Is there a way to create new files instead of overwriting? Say monday.csv, tuesday.csv etc or maybe {date}.csv

This is the command:

SQLCMD -S SQL-CLUST1 -Q "SELECT * FROM TABLE" -o "\\\PATH\output.csv" -W -s"," 


Step type: Operating system (CmdExec)

Run as : SQL Proxy

Note: The output file and append output to existing file is unavailable in the advanced tab.
Below script will help you. Just save it as a .bat file. 

@echo off
setlocal
set timehour=%time:~0,2%
sqlcmd -S SQL-CLUST1 -E -Q "SELECT * FROM TABLE" -o report-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.txt


If you have xp_cmdshell enabled, then it is much easier :

DECLARE       @sqlCommand   VARCHAR(max)
DECLARE       @filePath     VARCHAR(100)
DECLARE       @fileName     VARCHAR(100)

SET    @filePath = 'C:\Temp\'

SET    @fileName = 'Output_' +
       + CONVERT(VARCHAR, GETDATE(), 112) + '_' +
         CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +
         CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.txt'

SET    @sqlCommand =
       'SQLCMD -S server_name -E -d master -q "select @@servername" -o "' +
       @filePath + @fileName +
       '" -h-1'


PRINT       @sqlCommand

--EXEC   master..xp_cmdshell @sqlCommand
GO