deansaddigh Posted January 4, 2010 Share Posted January 4, 2010 how can i use substr to change what in $dateResults to a date format, because the db uses varchar for date as appose to DATE as its data type, dont ask me why someones set it to that. Heres the code where im echoing to dates. <td><?= "Check-in: " .$dateResults['arrival_date']. "<br />Checkout: " . $dateResults['departure_date'];?></td> Quote Link to comment https://forums.phpfreaks.com/topic/187117-turning-a-varchar-into-date-format-with-substr/ Share on other sites More sharing options...
PFMaBiSmAd Posted January 4, 2010 Share Posted January 4, 2010 dont ask me why someones set it to that.I'm not going to ask, but it is never too late to fix the problem. You can simply (backup your database first) create a new DATE column, populate the new column from the values in the existing column using a single UPDATE query and the mysql STR_TO_DATE() function to convert between the existing format and a DATE, change and test the existing queries to use the new DATE column, then remove the old column. For your current question, you haven't indicated what the current format is and what format you want, so it will be a little hard to directly help with the best way to accomplish the conversion. Quote Link to comment https://forums.phpfreaks.com/topic/187117-turning-a-varchar-into-date-format-with-substr/#findComment-988160 Share on other sites More sharing options...
deansaddigh Posted January 4, 2010 Author Share Posted January 4, 2010 Hi, Thanks for you reply, i cant do that to the db, ive already been told i cant at work. The date format comes out like this 20090327 Obviously i would like it like this 27/03/2009 Any help would be brilliant Quote Link to comment https://forums.phpfreaks.com/topic/187117-turning-a-varchar-into-date-format-with-substr/#findComment-988164 Share on other sites More sharing options...
PFMaBiSmAd Posted January 4, 2010 Share Posted January 4, 2010 Because your existing format is a valid date value, you can (this worked with a literal value and should work with values in a column) use the mysql DATE_FORMAT() function directly in your queries - SELECT DATE_FORMAT(existing_column_name, '%d/%m/%Y') Also, since the existing format is a valid date format it would INSERT directly into a DATE data type and you would use the same DATE_FORMAT code I just posted to retrieve values. So, there are no disadvantage in converting to a DATE data type. The advantages would be reduced storage requirements (your existing format uses 9 bytes and a DATE is 3 bytes) and faster database operation for every query that references your date values. Quote Link to comment https://forums.phpfreaks.com/topic/187117-turning-a-varchar-into-date-format-with-substr/#findComment-988178 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.