What data should I use for this query to return results?

This is my SQL query. I have data in my tables for this query. But this query doesn't return any results.

Select Prod_Batch_ID,tbl_product.product_name, Quantity,DATE_FORMAT(Produc开发者_如何学Pythontion_Date, '%W, %D %M %Y') from tbl_production,tbl_product where tbl_production.Product_ID  = tbl_product.Product_ID and (Production_Date between STR_TO_DATE('2010-01-01 00:00:00.000','%m/%d/%Y')and DATE_ADD(STR_TO_DATE('2010-12-12 00:00:00.000','%m/%d/%Y'), INTERVAL 1 DAY))

The Production_Date column in the table is of timestamp type. Is converting to date using str_to_date the problem here?

The problem is that you can't query against timestamp columns. To quote from SQL Server Books Online:

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

Your STR_TO_DATE format strings are wrong. Try

%Y-%m-%d

Or

%Y-%m-%d %h-%i-%s

You need to match the date format to the string you are passing in.