Insert a date into a sql query

Hi,

I have a problem with a query that I realised today. I can't integrate an input in the query.

This exec works :

e = exec(conn,'SELECT date_publication,to_days(horodatage)-to_days(date_publication), horodatage, valeur FROM point_courbe_ref p, mesure_ref m WHERE p.FK_mesure_ref = m.ID_mesure_ref AND m.FK_filiere_production = 6 AND m.FK_origine_mesure =10 AND horodatage BETWEEN {ts "2012-01-01 00:00:00"} AND {ts "2012-01-01 23:59:00"} AND to_days(horodatage)-to_days(date_publication)= 1 ');

e = fetch(e)

close(e)

but this one doesn't :

 date_debut = input('Entrer la date de debut au format : aaaa-mm-dd -> ','s')
 date_debut = datestr(date_debut,'yyyy-mm-dd' );
date_fin = input ('Entrer la date de fin au format : aaaa-mm-dd -> ','s');

 date_fin = datestr(date_fin,'yyyy-mm-dd' );

e = exec(conn,'SELECT date_publication,to_days(horodatage)-to_days(date_publication), horodatage, valeur FROM point_courbe_ref p, mesure_ref m WHERE p.FK_mesure_ref = m.ID_mesure_ref AND m.FK_filiere_production = 6 AND m.FK_origine_mesure =10 AND horodatage BETWEEN {ts "date_debut"} AND {ts "date_fin"} AND to_days(horodatage)-to_days(date_publication)= 1 ');

e = fetch(e)

close(e)

I tried several ways, but I don't know how to solve it ! Thanks :) !

[Marc Freed]

I have just solved this problem with some help from Matlab support.

>> mydata = fetch(conn,... ['SELECT colnames'... ' FROM tablename'... ' WHERE Date=''2010-12-31''']); >>

It's all about the correct placement of the single quotation marks and the spaces.

You must place two single quotation marks before the date and two after it with no spaces between them. If the "WHERE Date = ''yyyy-mm-dd'' is the end of your query, then you will need a third single quotation mark.

Also, you must not have a space at the end of each line. ' ... is incorrect. '... is correct.

You must have a space at the start of each line between the first single quotation mark and the first term. 'FROM ... is incorrect. ' FROM ... is correct.

Good luck.

Answer 1

[Titus Edelhofer]

Hi,

you want to take the variables date_debut and date_fin, not the string itself, so:

e = exec(conn,['SELECT date_publication,to_days(horodatage)-to_days(date_publication), horodatage, valeur FROM point_courbe_ref p, mesure_ref m WHERE p.FK_mesure_ref = m.ID_mesure_ref AND m.FK_filiere_production = 6 AND m.FK_origine_mesure =10 AND horodatage BETWEEN {ts "' date_debut '"} AND {ts "' date_fin '"} AND to_days(horodatage)-to_days(date_publication)= 1 ']);

Titus

[Alexandre]

Thanks Titus, it rocks ;) !

[Titus Edelhofer]

Your welcome. You might mark the question as answered if you like...