CODEWITHSUNDEEP
mysqldatabasemysql-5.5
rocky
rocky

Reputation: 435

SQL Query not running based on the BETWEEN condition

I have following SQL Query:

SELECT Count(*)

FROM   MyDB.mytable

WHERE date_dt BETWEEN '2013-12-09 00:00:00' AND '2013-12-09 23:59:59'

;

When I run the above query, I am getting count of the records of the results presents in the table. For some reason it's ignoring the BETWEEN condition I have included. Eventually, I have to use DELETE instead of SELECT and I would be doing something like the following:

DELETE  FROM MyDB.mytable
WHERE date_dt BETWEEN '2013-12-09 00:00:00' AND '2014-12-09 23:59:59'
;

Had I used DELETE, I would have accidentally deleted all the records from my table. Please let me know what am I doing wrong.

Upvotes: 0

Views: 34

Answers (2)

EternalHour
EternalHour

Reputation: 8621

The MySQL documentation says:

"For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE."

Worth a try.

SELECT Count(*)
FROM   MyDB.mytable
WHERE date_dt BETWEEN CAST('2013-12-09 00:00:00' AS DATETIME) 
AND CAST('2014-12-09 23:59:59' AS DATETIME);

Upvotes: 0

Ash
Ash

Reputation: 2585

Looks like you are trying to get records for a single day (2014-12-09) but your code says for last 366 days. May be i m wrong but it looks like you wanted :

 DELETE  FROM MyDB.mytable
  WHERE date_dt BETWEEN '2014-12-09 00:00:00' AND '2014-12-09 23:59:59'
  ;

Otherwise I can't see anything wrong with your query.

Upvotes: 1

Related Questions