David, I think it would help people like me (newbie) to know the exact statements. Though I could envisage what you would have done with my current learning, it would be good if I double check the statements that went there to fix it. Regards, Shreyas On Thu, Jun 17, 2010 at 7:07 PM, David Stoltz <Dstoltz@xxxxxxx> wrote: > Thanks all - I've fixed the problem. > > I fixed it by updating the php statement to write the date in a true SQL > date-ready format. Then I updated the invalid rows. > > Thanks all! > > > -----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 > -- Regards, Shreyas Agasthya
