Page 1 of 1

Problem with using "While / LIKE" with parameters

Posted: Sat Jan 06, 2007 1:37 pm
by michlcamp
I've got a good script that sorts orders based on date - using this query :

Code: Select all

SELECT * FROM orders WHERE (date like '01-%')
But now I need to sort them by year as well -
I tried using

Code: Select all

SELECT * FROM orders WHERE (date like '01-%-2007')
but get zero values returned.

anyone?

my question is: How to sort orders by month and year

the date field posted to my orders table uses this format: 01-01-2007

thanks in advance.
mc

Posted: Sat Jan 06, 2007 1:52 pm
by feyd
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:1. Select the correct board for your query. Take some time to read the guidelines in the sticky topic.
Is there a reason you're not using a native date format? The reason I ask this is because it makes it somewhat difficult to sort and select in date ranges like this.

SUBSTRING_INDEX() can be effectively used if you're using MySQL.

Posted: Sat Jan 06, 2007 1:54 pm
by jayshields
% is a wildcard character. You can only use it at the start or end of a string, not in the middle.

So to return all dates in Janurary 2007 you could change your query to this:

Code: Select all

SELECT * FROM `orders` WHERE `date` LIKE '01-%' AND `date` LIKE '%-2007'

Posted: Sat Jan 06, 2007 2:05 pm
by feyd
jayshields wrote:% is a wildcard character. You can only use it at the start or end of a string, not in the middle.

Code: Select all

mysql> select '10-12-11' like '10-%-11';
+---------------------------+
| '10-12-11' like '10-%-11' |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.03 sec)
:)

Posted: Sat Jan 06, 2007 2:48 pm
by michlcamp
jayshields wrote:% is a wildcard character. You can only use it at the start or end of a string, not in the middle.

So to return all dates in Janurary 2007 you could change your query to this:

Code: Select all

SELECT * FROM `orders` WHERE `date` LIKE '01-%' AND `date` LIKE '%-2007'

I tried changing my query that way but still got zero values -