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

Question

ARS 2020-1-23

1
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/501566-how-do-you-pass-matlab-date-or-user-input-to-sql-query-for-execution

回答： 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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Jakob B. Nielsen 2020-1-23

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/501566-how-do-you-pass-matlab-date-or-user-input-to-sql-query-for-execution#answer_411578

在 MATLAB Online 中打开

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 个评论
显示 -1更早的评论隐藏 -1更早的评论

ARS 2020-1-29

在 MATLAB Online 中打开

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.

请先登录，再进行评论。

Answer 2

ARS 2020-1-29

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/501566-how-do-you-pass-matlab-date-or-user-input-to-sql-query-for-execution#answer_412576

在 MATLAB Online 中打开

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.