How to query on part of a date column?

Barry Newton · *nix forums beginner Joined: 20 Jul 2006 Posts: 1

I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious tests
like:

Select * from Capclave2005reg
Where Year('Date Paid') = 2004;

return no rows. I can extract any piece of that date I want in a SELECT,
but can't seem to use it in a WHERE clause at all. There has to be
something really obvious that I'm missing?

Barry Newton

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Dan Buettner · *nix forums Guru Wannabe Joined: 13 Oct 2005 Posts: 119

Barry, I think you've got too many quotes in your SQL - the db is
trying to find the year from the string 'date paid'. You want to use
it as a column name, so drop the quotes:

Select * from Capclave2005reg
Where Year(Date Paid) = 2004;

If you've really got a space in your column name, try enclosing it in
backticks instead of quotes - ` instead of ', as in

Select * from Capclave2005reg
Where Year(`Date Paid`) = 2004;

Regards,
Dan

On 7/20/06, Barry Newton <bnewton@ashcomp.com> wrote:

mos · *nix forums Guru Wannabe Joined: 21 Feb 2005 Posts: 103

At 12:02 PM 7/20/2006, you wrote:

Martin Jespersen · *nix forums beginner Joined: 20 Jun 2006 Posts: 18

As long as backticks are used around fieldnames, spaces and/or reserved
words are fine, tho it does tend to create more work for the user ;)

mos wrote:

mos · *nix forums Guru Wannabe Joined: 21 Feb 2005 Posts: 103

At 06:20 PM 7/20/2006, Martin Jespersen wrote:

Google