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 Quote 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(). Quote 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 Quote 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. Quote Link to comment https://forums.phpfreaks.com/topic/60408-working-with-dates/#findComment-301178 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.