Search code examples
javascriptsqlweb-sqldate-comparison

compare date in sql statement

I am making an alert tool using web sql.

I want to selects all events that occur in a specific day. when I insert the event I insert the date as a date object with this format "2011-10-14" , What should I write in select statement to select all events that occur today ?

I make like this

 cur =new Date();
alert(cur);
db.transaction(function(tx){
    tx.executeSql("select NOTE_DESC,NOTE_DATE,NOTE_TIME,ALERT_TIME,NOTES_MORE_DETAILS from NOTES where NOTE_DATE = "+cur+" order by NOTE_TIME asc limit 0,1",[], alert("yes"),onError);
});

In which my query is:

select NOTE_DESC, NOTE_DATE, NOTE_TIME, ALERT_TIME, NOTES_MORE_DETAILS 
from NOTES 
where NOTE_DATE = "+cur+" 
order by NOTE_TIME asc 
limit 0,1

but an error message occur which is "near Oct: syntax error"

How should I make the date comparison in sql ??

Solution

  • I believe you want:

    NOTE_DATE = '"+cur.getFullYear()+'-'+(cur.getMonth()+1)+'-'+cur.getDate()+"'

    Note the ' wrapping the value for the query variable, and calling the cur date object's methods to construct the date.

    http://jsfiddle.net/yXgDw/

    Better yet, use a prepared query:

    http://www.w3.org/TR/webdatabase/#dom-sqltransaction-executesql

    http://www.w3.org/TR/webdatabase/#introduction