CODEWITHSUNDEEP
mysqlsql
Fustigador
Fustigador

Reputation: 6469

Select query in MySQL works in Workbench, but not in code

I have to get from Database all the users registered on a given date. So, i make the system to send an email. To select the users, i use this:

String query="Select mail from users where registered > "+"'"+datestring+"'"+";"

Where registered is the column that traks when the user registered, and datestring is a String, where the actual day is stored. So, I finally got a sentence like this one:

Select mail from users where registered > '2013-01-28';

The matter is... if I copy+paste the sentence in MySQL Workbench, it works like a charm. But, in code, it doesn't works, and the sentence is never launched. Any help?

In case it may helps, im using Grails (executeQuery(query) Method)

Upvotes: 1

Views: 627

Answers (2)

Andy Refuerzo
Andy Refuerzo

Reputation: 3332

Try casting your datestring to a date like:

String query="Select mail from users where 
    registered > CAST('"+datestring+"' as DATE);"

or as datetime:

String query="Select mail from users where 
    registered > CAST('"+datestring+"' as DATETIME);"

If those doesn't work, try STR_TO_DATE:

String query="Select mail from users where 
    registered > STR_TO_DATE('"+datestring+"', '%Y-%m-%d');"

This assumes that your table column for registered is of type DATE or DATETIME.

Upvotes: 1

Fustigador
Fustigador

Reputation: 6469

I finally used findByRegistered method, provided by Grails, instead of launching the SQL query directly, that way I was able to retrieve what I wanted. But i realized that I have to use an object Date in the findBy.... method. So, Andy, i think your answer would have been correct, so I upvote you.

Upvotes: 0

Related Questions