Fustigador Fustigador - 4 months ago 9
MySQL Question

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)

Answer

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.

Comments