Select Query with dynamic dates

2014 11 11
1 个回答
回答已采纳
更新时间：2014 11 11
4 次查看（30 天）

0 个投票

Hello
I am trying to execute a simple select query from my SQL database( dexplore on Matlab). I input the date in a gui, which takes in the date format with this command :
SDV = sprintf('%04d-%02d-%02d',SYYYY,SMM, SDD)
I get a date like this 2XXX - MM - DD
Then I need to import some data with date > SDV
My query is the following :
conn = database('', '', '', 'Vendor', 'MICROSOFT SQL SERVER', 'Server', 'TW\SQLEXPRESS', 'PortNumber', 1433, 'AuthType', 'Windows');
%Read data from database.
curs = exec(conn, ['SELECT XIV.Date'...
' , XIV.Value'...
' FROM master.dbo.XIV '...
' WHERE XIV.Date >=' SDV ]);
curs = fetch(curs);
The Query dont work because its not accepting the syntax at the end. When I use a fixed date, the syntax it takes is the following :
conn = database('', '', '', 'Vendor', 'MICROSOFT SQL SERVER', 'Server', 'TW\SQLEXPRESS', 'PortNumber', 1433, 'AuthType', 'Windows');
%Read data from database.
curs = exec(conn, ['SELECT VXX.Date'...
' FROM master.dbo.VXX '...
' WHERE VXX.Date > ''2004-05-20''']);
curs = fetch(curs);
close(curs);
This works but the date is not dynamic here.
Do you know how to write the query correctly in order for it to accept the SDV date?
Thank you very much
D

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

 采纳的回答

the cyclist
the cyclist 2014-11-11
编辑：the cyclist 2014-11-11

0 个投票

In place of
' WHERE VXX.Date > ''2004-05-20''']);
try
' WHERE VXX.Date > ''',SDV,'''']);
The problem with your original syntax is that it was failing to put in the required enclosing single quotes.

5 个评论
显示 3更早的评论 隐藏 3更早的评论

Davin
Davin 2014-11-11
Thanks 'the cyclist'. It works perfectly well.
the cyclist
the cyclist 2014-11-11
编辑：the cyclist 2014-11-11
I often write out the actual text like this first
' WHERE VXX.Date > ''2004-05-20''']);
then break the line up into the different strings as an intermediate step (to ensure I am getting the quotes right)
' WHERE VXX.Date > ''','2004-05-20','''']);
then finally replace the string with the variable
' WHERE VXX.Date > ''',SDV,'''']);
That intermediate step always involves putting in a pair of QUOTE-COMMA-QUOTE to set off the string you want.
Davin
Davin 2014-11-11
Yes thats a good technique. Because quite often, I have this issue on the syntax, I was trying stuff like [ { } ] but it didn't work. Then tried to replicate with the fixed date but it didnt work too, because it was not taking the value of SDV but as string of characters... thanks again
the cyclist
the cyclist 2014-11-11
Sorry to be commenting in drips and drabs, but another trick I do is to assign the query to a string variable:
queryText = 'SELECT * FROM TABLE'
Then after I build the query (possibly replacing with a variable, as you have done), then I'll print queryText to the MATLAB screen, just to make sure it is the exact string that needs to be sent in SQL.
Davin
Davin 2014-11-11
yeah good technique too. In fact, to know the syntax, i went on the database explorer, on the small sql query on top, i put the fixed date, normally if your syntax is good here, for example for dates, you need to put something like '2004-04-01' then it will filter in yr table.You have an import button, with a down button, u click on generate script... MATLAB writes you the query for you, it can put on the correct path when it comes to writing the query directly. But as i was using SDV it was a bit more tricky. No issues at all for commenting. Sharing experiences is very important...

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by