CODEWITHSUNDEEP
mysql
Ryan Jones
Ryan Jones

Reputation: 447

MySQL TimeStamp query

I am trying to search for studentID within a date range. I only have one date in my database, therfore i only want the users to input one date, rather than having them input a start date and an end date for: WHERE timeStamp BETWEEN startDate AND endDate

So i am trying this...

SELECT * FROM scansTable
INNER JOIN registeredUsers ON scansTable.studentID = registeredUsers.id
INNER JOIN labSession ON scansTable.labSessionID = labSession.id
INNER JOIN staffTable ON labSession.lecturer = staffTable.id
INNER JOIN unitTable ON labSession.unit = unitTable.id
WHERE studentID = '10'
AND labSession.StartTimeStamp BETWEEN '2011 -05 -30'+00:00:00
AND '2011 -05 -30'+23:59:59;

But it is not returning anything when i know for sure there is a student of id 10 and that date range in the database

Am i doing the +00:00:00 wrong??

thanks

Upvotes: 0

Views: 5260

Answers (4)

Tadeck
Tadeck

Reputation: 137300

It seems you are using something like '2011 -05 -30'+00:00:00, where you should use '2011-05-30 00:00:00' (and make corresponsing changes to the second condition), because TIMESTAMP format (I assume this field is in this format) is YYYY-MM-DD HH:MM:SS.

Did it help? If not, give use the definition of the table plus the example row (at least both timestamp columns).

EDIT:

If you wanted to concatenate, you should have used CONCAT() function (see MySQL's documentation). It would look like this:

CONCAT('2011-05-30',' 00:00:00')

or, more meaningfully:

CONCAT_WS(' ','2011-05-30','00:00:00')

Upvotes: 2

Randy
Randy

Reputation: 16677

between might not include that lower bound as an =

i believe it does include the upper bound as =, this might differ depending on the database.

Upvotes: 0

AJ.
AJ.

Reputation: 28174

Remove the spaces and plus symbols:

BETWEEN '2011-05-30 00:00:00' AND '2011-05-30 23:59:59'

Upvotes: 4

stevevls
stevevls

Reputation: 10843

If you haven't changed the default date format, remove the + signs and the extra spaces.

labSession.StartTimeStamp BETWEEN '2011-05-30 00:00:00' AND '2011-05-30 23:59:59';

Also, I don't know if it's a byproduct of the copy/paste, but MySQL won't even run the query as-is. The time portion of your timestamp needs to be within the quotes.

Upvotes: 2

Related Questions