Automatically naming SQL output files with date in sqlcmd

Data and Databases
1

Goal:

I need to make a CSV file every week from my SQL data. I want the filename to include the current date automatically.

Right now, I’m using this command:

sqlcmd -S MYSERVER -i C:\Scripts\query.sql -o C:\Exports\report_DATE.csv

But it’s not working like I want. How can I change this so the DATE part actually shows today’s date? I’m not sure if I need to use some kind of variable or if there’s a built-in way to do this in sqlcmd.

Has anyone done something like this before? Any help would be great. Thanks!

2

ooh, interesting question! have you considered using a batch file? you could do something like:

@echo off
for /f “tokens=1-3 delims=/” %%a in (‘“%date%”’) do (
set filename=C:\Exports\report_%%c%%a%%b.csv
)
sqlcmd -S MYSERVER -i C:\Scripts\query.sql -o %filename%

this might work? what do you think? would love to hear if you try it out!

3

hey there! i’ve got another idea for ya. why not try using xp_cmdshell in your sql script? something like:

DECLARE @cmd varchar(500)
SET @cmd = ‘sqlcmd -S MYSERVER -i C:\Scripts\query.sql -o C:\Exports\report_’ + CONVERT(varchar(8), GETDATE(), 112) + ‘.csv’
EXEC xp_cmdshell @cmd

might do the trick. just make sure xp_cmdshell is enabled first. good luck!

4

I’ve tackled this issue before, and here’s a solution that works well:

You can use PowerShell to generate the date-stamped filename and then pass it to sqlcmd. Here’s an example:

$date = Get-Date -Format “yyyyMMdd”
$output = “C:\Exports\report_$date.csv”
sqlcmd -S MYSERVER -i C:\Scripts\query.sql -o $output

This script creates a variable with today’s date in YYYYMMDD format, constructs the output filename, and then runs sqlcmd with the dynamic filename.

You can save this as a .ps1 file and schedule it to run weekly. It’s a reliable method that ensures your reports are always named with the current date.