Last time I posted about How you can add date/time to output file name, in which I used xp_cmdshell to execute the BCP/SQLCMD command using TSQL, which means we need to have xp_cmdshell server feature enabled for that solution work. There is a workaround available to that solution when xp_cmdshell is not enabled.
You can also run BCP/SQLCMD command on command prompt (cmd.exe) and append date/time to output file name. You can use below commands:
SQLCMD command to add date/time to output filename:
SQLCMD -S (local) -E -d SqlAndMe -q "EXEC ExportData" -o MyFile_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.txt
BCP command to add date/time to output filename:
bcp "EXEC ExportData" queryout MyFile_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.txt -S (local) -T -d SqlAndMe -c
You can use ECHO command to verify the file name:
How this works:
On command prompt %date% return current date in short format. The ":~6,4" part is like a SUBSTRING function which returns 4 characters starting from position 6, which returns year. Similarly, we are retrieving month, day, hour, minutes using same function and appending all of this together to generate the file name in format "MyFile_YYYYMMDD_HHMM.txt"
Hope This Helps!
Filed under: Management Studio, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data