Date conversion in load record does not work

[hardina09]

When below is executes, it gives me error:

Query must start with 'SELECT'
Wrapped java.lang.IllegalArgumentException: Query must start with 'SELECT'

Code: Select all

var sql=" SELECT TRANS_ID FROM JOBWORKS " +
" WHERE EMPID='" + varEmpId + "'" +
" AND CONVERT(VARCHAR(30),STARTDATES,121) BETWEEN CONVERT(VARCHAR(30),'" + startDate + "',121) AND CONVERT(VARCHAR(30),'" + endDate + "',121) " +
" ORDER BY STARTDATES"
foundset.loadRecords(sql,null)

I don't know where I went wrong. Any Suggestion.

[jgarfield]

You've got an extra space at the beginning of your query.

Code: Select all

var sql="SELECT TRANS_ID FROM JOBWORKS " +
" WHERE EMPID='" + varEmpId + "'" +
" AND CONVERT(VARCHAR(30),STARTDATES,121) BETWEEN CONVERT(VARCHAR(30),'" + startDate + "',121) AND CONVERT(VARCHAR(30),'" + endDate + "',121) " +
" ORDER BY STARTDATES"
foundset.loadRecords(sql,null)

Should work better.

Really you should parameterize your query so it looks more like this though:

Code: Select all

var sql="SELECT TRANS_ID FROM JOBWORKS " +
" WHERE EMPID= ? " +
" AND CONVERT(VARCHAR(30),STARTDATES,121) BETWEEN CONVERT(VARCHAR(30), ?,121) AND CONVERT(VARCHAR(30), ?,121) " +
" ORDER BY STARTDATES"
foundset.loadRecords(sql,[varEmpID, startDate, endDate])

[hardina09]

Awesome! Thanks jgarfield.