How do you pass MATLAB date (or user input) to SQL query for execution

7 次查看（过去 30 天）
ARS
ARS 2020-1-23
回答： ARS 2020-1-29
>> myDate= datetime('2020-01-01');
>> selectquery = 'SELECT * FROM myTABLE WHERE reportDate >= '''myDate''' AND portfolio =''ABC'' ';
>> Data = select(conn,selectquery);
I have tried a few quotation mark sequences around 'myDate' but doesn’t seem to be working. My database is MS SQL Server 2013. The Query works if I specify a string date i.e. '2020-01-01' .
Your help is appreciated.

请先登录，再回答此问题。

回答（2 个）

Jakob B. Nielsen
Jakob B. Nielsen 2020-1-23
I remember struggling alot with this when I first started out grabbing data from SQL servers. I found that an approach where I constructed a series of strings in the following manner worked for me, maybe it will for you as well. For my server, the datetime format is yyyy-mm-dd. I feel like it "should be" easier than this, but once I found out that this worked I just went with it ;)
space={' '}; %for some reason, my query wont fire if there isnt a cell space string inserted. I actually dont even know why :)
selectquery=strcat('SELECT *',space);
connquery=strcat('FROM myTABLE ',space);
datestart=strcat('WHERE reportDate > ''2019-10-01 00:00:00.000'' ',space);
dateend=strcat('AND reportDate < ''2019-12-31 00:00:00.000'' ',space);
Data=select(conn,[selectquery{:} ...
connquery{:}
datestart{:}
dateend{:}]);
  1 个评论
ARS
ARS 2020-1-29
Hi, Thanks for your answer but this one didn't work for me.
I used the following and it works:
selectquery = 'SELECT * FROM myTable WHERE myDate BETWEEN ''%s'' AND ''%s'' ';
myQuery = sprintf(selectquery, myDateOld, myDateNew);
myData = select(conn,myQuery);
This works great.

请先登录，再进行评论。

ARS
ARS 2020-1-29
Hi, Thanks for your answer but this one didn't work for me.
I used the following and it works:
selectquery = 'SELECT * FROM myTable WHERE myDate BETWEEN ''%s'' AND ''%s'' ';
myQuery = sprintf(selectquery, myDateOld, myDateNew);
myData = select(conn,myQuery);
This works great.

类别

在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

产品


版本

R2019b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by