I did this, sorry for bad format. This will do it for you. SELECT CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) AS docdate FROM `tempdate` WHERE CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) < '060703' ORDER BY CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) DESC LIMIT 1
/Peter -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 02, 2006 3:28 PM To: Peter Lauri; mysql@lists.mysql.com Subject: Re: Getting the previous months documents My sql is not so great. The dates are entered through a dropdown box in the format dd/mm/yyyy (uk date format) as a VARCHAR. I could use strrev and str_replace (PHP) to get it in the correct format but will the mysql query you send work on a VARCHAR which it is it present or will I have to change the field to DATE? Ross ----- Original Message ----- From: "Peter Lauri" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Tuesday, May 02, 2006 9:06 AM Subject: RE: Getting the previous months documents > You should start by using MySQL date as the standard for date: YYYY-MM-DD > > After that it is simple: > > SELECT * FROM documents WHERE doc_date<2006-05-02 ORDER BY doc_date DESC > LIMIT 1; > > Othervise you could use MySQL function to take sub strings and create a > field in the query that extract it as 06-05-02 and order by that. > > /Peter > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 02, 2006 3:04 PM > To: mysql@lists.mysql.com > Subject: Getting the previous months documents > > I have a database that stores documents relating to meetings. They have > all > the usual stuff agenda, minutes etc. There are 3 paper types for each date > agenda (1 only), minutes (1 only), and a bunch of general documents titled > 'papers'. > > I need to display all the documents for a specific date which is easy but > I > also need to retrieve the minutes for the PREVIOUS meeting which is > proving > more difficult. The documents are stored by date in the format dd/mm/yy. > If > someone wants the documents from 02/05/06 how do I find the minutes for > the > previous date when I do not know when it is? > > Can I do this with mysql? Or will it be better with mktime and some php? > > > > -- > -- Table structure for table `board_papers` > -- > > CREATE TABLE `board_papers` ( > `id` int(4) NOT NULL auto_increment, > `doc_date` varchar(10) NOT NULL default '0000-00-00', > `article_type` enum('agenda','minutes','paper') NOT NULL default > 'agenda', > `fileName` varchar(50) NOT NULL default '', > `fileSize` int(4) NOT NULL default '0', > `fileType` varchar(50) NOT NULL default '', > `content` blob NOT NULL, > PRIMARY KEY (`id`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; > > > > -- > 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]
