CODEWITHSUNDEEP
mysql
Viral Bhoot
Viral Bhoot

Reputation: 357

Getting null result although there is in database

Here, I am using mysql query as it shows null result or empty result although there are records in database. So, where is the problem actually?

SELECT `fx_tasks`.*, `fx_assign_tasks`.`assigned_user` 
FROM (`fx_tasks`) 
LEFT JOIN `fx_assign_tasks` 
  ON `fx_assign_tasks`.`task_assigned` = `fx_tasks`.`t_id` 
WHERE `fx_tasks`.`project` = 55 
  AND str_to_date(`fx_tasks`.`start_date`, "%d-%m-%Y") 
      BETWEEN "08-09-2015" AND "28-09-2015" 
ORDER BY `fx_tasks`.`t_id` DESC

Upvotes: 1

Views: 39

Answers (2)

Shadow
Shadow

Reputation: 34231

The problem is probably with the str_to_date(fx_tasks.start_date, "%d-%m-%Y") BETWEEN "08-09-2015" AND "28-09-2015" part (not with the double quotes, though). The start_date is converted to date, which is in YYYY-MM-DD format in mysql, and then you compare it to a string containing a date in a different format. No records would satisfy this criterion.

Change the dates in the above criterion to YYYY-MM-DD format:

str_to_date(`fx_tasks`.`start_date`, "%d-%m-%Y") BETWEEN "2015-09-08" AND "2019-09-28"

Upvotes: 2

gen_Eric
gen_Eric

Reputation: 227260

Dates in MySQL need to be in the Y-m-d format.

str_to_date(`fx_tasks`.`start_date`, "%d-%m-%Y") 
    BETWEEN "2015-09-08" AND "2019-09-28"

From the docs (http://dev.mysql.com/doc/refman/5.5/en/datetime.html):

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format.

When you use str_to_date, it uses the format string to parse the value from the db and then returns you a DATE value - in Y-m-d format.

P.S. If fx_tasks.start_date is already a DATE type, then you don't need str_to_date.

P.P.S. If you want to print a DATE in a different format, use date_format().

Upvotes: 1

Related Questions