How to use sqlcmd to create file names for output based on the day of the week or the date?

Question

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.

• Colin 't Hart: Which DBMS? SQL Server? SQL is a language, not a DBMS.
• Rakesh: @Colin Yes its MS SQL Server.

Answer 1

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 

Answer 2

You can add another step after that SQLCMD that performs renaming your outputfile in another CmdExec step type:

ren myfile.csv myfile_%date:~4,2%-%date:~7,2%-%date:~10,4%%time:~0,2%%time:~3,2%%time:~6,5%.csv