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
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 -