SQLCMD – Add Date to CSV in Command Prompt and Job

csvdatesql-server-2008-r2sqlcmd

I've been battling for hours to add a date to a CSV name:
For brevity, Im just taking the first character of the date here.

In SQL Command, this works:

sqlcmd -S localhost -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "C:\sqlcmd%date:~1,1%.csv"

But when I try to use that exact same code inside the step of a job, the filename is

sqlcmd_%date

I cant run xp_commandShell, is there a way to get the dynamic date working in SQLCmd please?
I've read through so many articles, eg.

Best Answer

Try Agent Tokens instead, eg

sqlcmd -S .\sql2012 -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "c:\temp\sqlcmd$(ESCAPE_NONE(DATE)).csv"

This worked for me. Just a reminder, bcp is a more appropriate tool for this kind of extract; you won't have the header and "row(s) affected" problems which you're not dealing with here.

Related Question