How to query on part of a date column?
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/[EMAIL PROTECTED]
Re: How to query on part of a date column?
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 [EMAIL PROTECTED] wrote: 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
At 12:02 PM 7/20/2006, you wrote: 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 Barry, I strongly recommend getting rid of the spaces in your column/table names and replace them with an underscore character _. You are only creating problems for yourself if you leave the blanks in the names. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
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: At 12:02 PM 7/20/2006, you wrote: 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 Barry, I strongly recommend getting rid of the spaces in your column/table names and replace them with an underscore character _. You are only creating problems for yourself if you leave the blanks in the names. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
At 06:20 PM 7/20/2006, Martin Jespersen wrote: 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 ;) Correct. But how many people want to create more work for themselves? A show of hands please!bg Mike mos wrote: At 12:02 PM 7/20/2006, you wrote: 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 Barry, I strongly recommend getting rid of the spaces in your column/table names and replace them with an underscore character _. You are only creating problems for yourself if you leave the blanks in the names. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]