Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
michlcamp
Forum Commoner
Posts: 78 Joined: Mon Jul 18, 2005 11:06 pm
Post
by michlcamp » Sat Jan 06, 2007 1:37 pm
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
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Sat Jan 06, 2007 1:52 pm
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.
jayshields
DevNet Resident
Posts: 1912 Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England
Post
by jayshields » Sat Jan 06, 2007 1:54 pm
% 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'
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Sat Jan 06, 2007 2:05 pm
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)
michlcamp
Forum Commoner
Posts: 78 Joined: Mon Jul 18, 2005 11:06 pm
Post
by michlcamp » Sat Jan 06, 2007 2:48 pm
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 -