Here's my approach to this problem, and how I am planning on fixing this - tell me what you think if this will work... I need the format in the database to be "1/14/2010 3:25:58 PM" There's not a ton of records that need to be updated, so I was going to: 1) Change the PHP function that is writing the date format incorrectly to the above format 2) Then manually go through the records and update the incorrect dates 3) Then change the datatype in the DB for that column from varchar to datetime Far as I can see, this should work. This is why I hate inheriting other people's stuff...not to mention I'm a newbie, so that doesn't help ;-) Thanks! -----Original Message----- From: Richard Quadling [mailto:rquadling@xxxxxxxxx] Sent: Thursday, June 17, 2010 8:47 AM To: David Stoltz Cc: ash@xxxxxxxxxxxxxxxxxxxx; php-general@xxxxxxxxxxxxx Subject: Re: Date Conversion Problem On 17 June 2010 13:40, David Stoltz <Dstoltz@xxxxxxx> wrote: > I would agree with you, but I have no control on inherited web apps. > > > > I now need to concentrate on trying to fix this. > > > > From: Ashley Sheridan [mailto:ash@xxxxxxxxxxxxxxxxxxxx] > Sent: Thursday, June 17, 2010 8:38 AM > To: David Stoltz > Cc: php-general@xxxxxxxxxxxxx > Subject: Re: Date Conversion Problem > > > > On Thu, 2010-06-17 at 08:35 -0400, David Stoltz wrote: > > > PHP newbie here... > > > > I have some PHP code writing the date/time into a MS SQL 2000 database > like this: > > > > date('l jS \of F Y h:i:s A') > > > > So the text it writes into the DB is like: Thursday 15th of April 2010 > 10:13:42 AM > > > > The database field is defined as varchar, not datetime...so it's a > string essentially... > > > > How in the world do I do a date conversion on this? I've tried things > like: > > > > select * from table where convert(datetime,fieldname) >= '6/10/2010' > > (where fieldname is the string in question) > > > > Which results in "Syntax error converting datetime from character > string." > > > > So I guess I have two questions: > > > > 1) Can I write a SQL query that will convert this properly into a > datetime? > > 2) If not, I guess I'll need to change the code to write the date > differently into the system, how should this statement be changed to > allow for proper conversion? date('l jS \of F Y h:i:s A') > > > > Thanks for any help! > > > > It's best to store the date as a date rather than a string, as it avoids the sorts of problems you're seeing now. > > Thanks, > Ash > http://www.ashleysheridan.co.uk > > > > > > The "fix" is most likely to be ... 1 - Convert the string date using PHP's strtotime() function to populate a new column on the DB. 2 - Find the code that inserts/updates the date string and add the new column to the insert/update statement. That will preserve the current app and allow you to have the new column for new work. Just remember, if _YOU_ update the new column, you must also update the original date string also. -- ----- Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling
