How to pass date in sqlcmd

Transact-SQL
, ,
1

Hi Team,

Please look into below code anda advise me how to pass currentdate in out parameter of sqlcmd

code--
Declare
@FDate as varchar(10)

set @Fdate = (SELECT CONVERT(INT,CONVERT(CHAR(8),GETDATE()-1,112))) 
EXEC MASTER.DBO.XP_CMDSHELL 'SQLCMD -S .\ -d SFTPWELSPUN -E -Q "SELECT MAINTAG,INVOICENUMBER,INVOICEDATE,TYPE,SERVICETYPE,ITENARY,DAYS,ADVANCE_PURCHASE,TICKET_TYPE,OPTION1,OPTION2,OPTION3,NET_LOST_SAVING,LOWEST_FARE,HIGHEST_FARE FROM DBO.TBL_SFTPWELSPUN_MASTER where convert(date,invoicedate)=convert(date,getdate()-2) ORDER BY INVOICEDATE " -s "," -o"E:\Report\Details_'+@FDATE+'.CSV"'

error while executing the code--
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '+'.

Please advise

2

It's a problem with EXEC itself. It doesn't allow you to use formulas where parameters belong.

So do this... the idea is to create the complete command in a variable and then execute it as a whole. I also change a couple of thing that weren't hurting anything but weren't needed.

DECLARE  @FDate CHAR(8) = CONVERT(CHAR(8),GETDATE()-1,112)
        ,@Cmd   VARCHAR(8000)
;
 SELECT @Cmd = 'SQLCMD -S .\ -d SFTPWELSPUN -E -Q "SELECT MAINTAG,INVOICENUMBER,INVOICEDATE,TYPE,SERVICETYPE,ITENARY,DAYS,ADVANCE_PURCHASE,TICKET_TYPE,OPTION1,OPTION2,OPTION3,NET_LOST_SAVING,LOWEST_FARE,HIGHEST_FARE FROM DBO.TBL_SFTPWELSPUN_MASTER where convert(date,invoicedate)=convert(date,getdate()-2) ORDER BY INVOICEDATE " -s "," -o"E:\Report\Details_'+@FDATE+'.CSV"'
;
   EXEC xp_CmdShell @Cmd
;