johnnybravo Posted July 17, 2007 Share Posted July 17, 2007 Hi I am currently building a Content Managment Driven website using PHP and MySQL. I have come across a problem with my dates... Basically I need to be able to insert a custom date into my database and then retrieve the date and format it in different ways. For example: 17 July 2007 Tuesday 17 July 2007 17/07/2007 Is this possible from one field with the date in MySQL? What format do i need to insert the date into the database? How can i format the date in these different formats? The one really stumping me is the Day of the week??? My next problem is I want to sort these fields, first by the month and then by the date of the month i hope someone can shed some light thanks in advance John Link to comment https://forums.phpfreaks.com/topic/60408-working-with-dates/ Share on other sites More sharing options...
fenway Posted July 17, 2007 Share Posted July 17, 2007 Store as DATE(TIME), format pretty using DATE_FORMAT(). Link to comment https://forums.phpfreaks.com/topic/60408-working-with-dates/#findComment-300540 Share on other sites More sharing options...
johnnybravo Posted July 17, 2007 Author Share Posted July 17, 2007 Fantastic, got that working thanks! I have another question now... I want to be able to filter the records... I would like to be able to limit the results to the first entry for each month, is that possible? Code so far is: SELECT ID, DATE_FORMAT(Date, '%W %D %M %Y') as Date from tbltest WHERE DATE_FORMAT(Date, '%j') > '$CurrentDate' ORDER BY DATE_FORMAT(Date, '%j') ASC LIMIT 5 thanks again for your help so far John Link to comment https://forums.phpfreaks.com/topic/60408-working-with-dates/#findComment-300639 Share on other sites More sharing options...
fenway Posted July 18, 2007 Share Posted July 18, 2007 I caution you against using a reserved keyword -- Date -- for your field name.. you're getting away with it because it's inside an expression. Pick another name for your column. Also, I find it easier to use DAYOFYEAR() rather than the corresponding date_format() for clarity -- though I'm not sure why you just don't use a proper date here, and set $currentDate appropriately -- that way, mysql can use any available index on this column. Same goes for the order by. I have another question now... I want to be able to filter the records... I would like to be able to limit the results to the first entry for each month, is that possible? Sure, there are a few ways to do that, but it's not trivial -- you'll need to group by each month, but also by year, too. Link to comment https://forums.phpfreaks.com/topic/60408-working-with-dates/#findComment-301178 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.