BAT file to pull data only from yesterday into a csv file-CodePudding

I have a SQL table named Response_Master_Incident, and I want to copy the data from yesterday, into a CSV file. The field with the date in it, is called Response_Date. This is in a datetime format of YYYY-MM-DD 00:00:00.000. I wrote the following:

SQLCMD -S cadarchive -d Reporting_System -E -Q "Select ID,Master_Incident_Number,Response_Date,Problem,MethodOfCallRcvd,EMD_Used,Determinant,ProQa_CaseNumber,ProQa_CaseNumber_Fire,ProQa_CaseNumber_Police,MachineName,CallTaking_Performed_By FROM [Response_Master_Incident] where  [Response_Date]>=cast(dateadd(day,-1, CURRENT_TIMESTAMP) as date)
 and [Response_Date] < cast(current_timestamp as date) and EMD_Used=1" -s "," -o "C:\ProQASQL\Exported_Table.csv"
@pause

But when I run this, I get the error:

C:\ProQASql>and [Response_Date]  as date) and EMD_Used=1" -s "," -o "C:\ProQASQL\Exported_Table.csv" 0<cast(current_timestamp
The system cannot find the file specified."

Can someone point out what I am doing wrong?

If I remove the clause about the date and just leave it as Where EMD_Used=1, the bat file runs correctly.

CodePudding user response：

I resolved this issue by using the following:

SQLCMD -S cadarchive -d Reporting_System -E -Q "select ID,Master_Incident_Number,Response_Date,Problem,MethodOfCallRcvd,EMD_Used,Determinant,ProQa_CaseNumber,ProQa_CaseNumber_Fire,ProQa_CaseNumber_Police,MachineName,CallTaking_Performed_By FROM [Response_Master_Incident] WHERE CONVERT(date, Response_Date) = DATEADD(day, -1, CONVERT(date, getdate())) and EMD_Used=1" -s "," -o "C:\ProQASQL\Exported_Table.csv"