Substr within query?

solfer

I'm a noob so this might be an obvious one to most, but here goes...

One of my tables holds a date field (yyyy-mm-dd) on each row. I want to query the table and only retrieve rows where the month matches a passed in variable.

Can I use the substr function within my sql query? I was thinking I'd need to do something like this, but I can't seem to get it to work...

$v = '09' // just as an example

"SELECT SUBSTR(date, 4, 2) AS date, firstname, lastname, FROM mytable WHERE date = '$v'"

TIA
Sol

tomhath

Isn't it spelled SUBSTRING, not SUBSTR?

http://www.mysql.com/doc/en/String_functions.html

solfer

I've tried both substring and substr but it's falling over on the WHERE clause and I can't see why...

Lars_Berg

To get a month value from a date you need to use the extract function.

$q = "select * from mytable  where extract(month from date) = " . $monthValue ;

tomhath

Assuming your "date" column is really a string (type is char or varchar) there are two things wrong.

First, the word "date" is reserved, so naming a column "date" has confused mysql; it thinks you are talking to the type 'date'.

Second, the substring function in SQL is 1 based, so the select should be something like:

SELECT SUBSTRING(myDate, 5, 2) AS myMonth, firstname, lastname, FROM mytable WHERE myDate = '$v'

Lars_Berg

Date is not reserved in MYSQL, according to the docs.

http://www.mysql.com/doc/en/Reserved_words.html

solfer

Originally posted by Lars Berg
To get a month value from a date you need to use the extract function.
$q = "select * from mytable  where extract(month from date) = " . $monthValue ;
[/B]

Thanks Lars, that's working a treat now 🙂

tomhath, thanks for pointing out that the substring function in SQL is 1 based - I realised after my original post that I'd made a mistake there!

tomhath

Date is not reserved in MYSQL, according to the docs.

Hmmm. It's in the table of reserved words in the docs I have (from 3.23.41). This change happened going from 3.20 to 3.21. Was "date" removed in 4.0?

The following changes may affect queries in old applications:

HAVING must now be specified before any ORDER BY clause.
The parameters to LOCATE() have been swapped.
There are some new reserved words. The most notable are DATE, TIME, and TIMESTAMP.

http://www.mysql.com/doc/en/Upgrading-from-3.20.html

Although mysql will let you use the words as column names, I wouldn't.