Rab
Member
Registered: 10th Jun 07
Location: Alloa, Scotland Drives: Subaru Hawkeye STi
User status: Offline
|
Doing a project at uni, and I am trying to query the table for sales made within a certain time.
I have been using this:
code:
SELECT `transaction_number` , `user_id` , `item_number` , `date_of_trans` , `total_cost`
FROM `cstransactions`
WHERE `date_of_trans` between `2008-04-01` and `2008-05-30`
And I get this error:
MySQL said: Documentation
#1054 - Unknown column '2008-04-01' in 'where clause'
The table has these values:
date_of_trans
2008-05-01
2008-04-03
2008-05-02
I'm not any good at MYSQL, just a beginner, but I can't see where its going tits up 
Any ideas? Cheers! 
[EDIT] Just managed to sort of fix it, I just removed the ` from around the data. Im guessing when you use ` it looks to it as if it was a table. But there is still a slight problem - it displays no data, but there is actually data in there. The field data type is DATE btw. Does it mess up when using between dates? Been doing other betweens fine.
[Edited on 02-05-2008 by RabR89]
|
ed
Member
Registered: 10th Sep 03
User status: Offline
|
I'm pretty sure that you can't use a BETWEEN statement in the way you want using the date formatted in that way....
What you want to do is to set the date_of_trans to a date in the MySQL database. Then when you put the dates into the database you need to have them formatted as a UNIX timestamp.
|
Rab
Member
Registered: 10th Jun 07
Location: Alloa, Scotland Drives: Subaru Hawkeye STi
User status: Offline
|
Okay, tried what you suggested, but when I set it to the UNIX_TIMESTAMP the dates are set to 0000-00-00. Then when I change them to what I need, and click save, when I Browse the table again, they seem to go back to 0000-00-00.
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Or you can cast the string as a date in the query....
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
SELECT *
FROM cstransactions
WHERE date_of_trans BETWEEN CAST('2008-04-01' AS DATE) AND CAST('2008-05-30' AS DATE)
|
