Date Conversion Problem
in [General]
|
Prev: Unit testing in PHP
Next: curl help or other suggestion
From: Richard Quadling on 17 Jun 2010 08:46 On 17 June 2010 13:40, David Stoltz <Dstoltz(a)shh.org> 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(a)ashleysheridan.co.uk] > Sent: Thursday, June 17, 2010 8:38 AM > To: David Stoltz > Cc: php-general(a)lists.php.net > Subject: Re: [PHP] 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
From: "David Stoltz" on 17 Jun 2010 08:54 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(a)gmail.com] Sent: Thursday, June 17, 2010 8:47 AM To: David Stoltz Cc: ash(a)ashleysheridan.co.uk; php-general(a)lists.php.net Subject: Re: [PHP] Date Conversion Problem On 17 June 2010 13:40, David Stoltz <Dstoltz(a)shh.org> 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(a)ashleysheridan.co.uk] > Sent: Thursday, June 17, 2010 8:38 AM > To: David Stoltz > Cc: php-general(a)lists.php.net > Subject: Re: [PHP] 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
From: "David Stoltz" on 17 Jun 2010 09:37 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(a)gmail.com] Sent: Thursday, June 17, 2010 8:47 AM To: David Stoltz Cc: ash(a)ashleysheridan.co.uk; php-general(a)lists.php.net Subject: Re: [PHP] Date Conversion Problem On 17 June 2010 13:40, David Stoltz <Dstoltz(a)shh.org> 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(a)ashleysheridan.co.uk] > Sent: Thursday, June 17, 2010 8:38 AM > To: David Stoltz > Cc: php-general(a)lists.php.net > Subject: Re: [PHP] 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
From: Shreyas Agasthya on 17 Jun 2010 11:41 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(a)shh.org> 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(a)gmail.com] > Sent: Thursday, June 17, 2010 8:47 AM > To: David Stoltz > Cc: ash(a)ashleysheridan.co.uk; php-general(a)lists.php.net > Subject: Re: [PHP] Date Conversion Problem > > On 17 June 2010 13:40, David Stoltz <Dstoltz(a)shh.org> 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(a)ashleysheridan.co.uk] > > Sent: Thursday, June 17, 2010 8:38 AM > > To: David Stoltz > > Cc: php-general(a)lists.php.net > > Subject: Re: [PHP] 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
From: "David Stoltz" on 18 Jun 2010 07:00
No Problem Shreyas, My original PHP code was writing the date like this: echo date('l jS \of F Y h:i:s A'); // - Thursday 17th of June 2010 08:58:02 AM I changed it to this: echo date('n\/j\/Y h:i:s A');// - 6/17/2010 08:58:02 AM Now that it was writing to the database correctly, I ran the following script below to update all the rows that were improperly formatted. After the below script ran, I didn't even have to convert the column to datetime, since the SQL convert statement would now easily convert the string to date - although the proper thing to do would be to convert the column to date/time type. The below script was written in classic ASP- I've added a commented out string below each step so you can see the changes being made to the string: <% 'This script was used to convert invalid php dates into true date formats in the DB set conn = Server.CreateObject("ADODB.Connection") conn.open = "My DB Conncetion String" set rs = conn.execute("SELECT id, employee_signed_on FROM TABLE1 WHERE employee_signed_on is not null") do while not rs.eof id = rs("id") temp = rs("employee_signed_on") oldDate = rs("employee_signed_on") '''''''''''''''''''''''''''''''''''''Thursday 17th of June 2010 08:58:02 AM oldDate = replace(oldDate,"Monday ","") oldDate = replace(oldDate,"Tuesday ","") oldDate = replace(oldDate,"Wednesday ","") oldDate = replace(oldDate,"Thursday ","") oldDate = replace(oldDate,"Friday ","") oldDate = replace(oldDate,"Saturday ","") oldDate = replace(oldDate,"Sunday ","") '''''''''''''''''''''''''''''''''''''17th of June 2010 08:58:02 AM oldDate = replace(oldDate,"th of ","/") oldDate = replace(oldDate,"nd of ","/") oldDate = replace(oldDate,"st of ","/") oldDate = replace(oldDate,"rd of ","/") '''''''''''''''''''''''''''''''''''''17/June 2010 08:58:02 AM oldDate = replace(oldDate,"January ","1/") oldDate = replace(oldDate,"February ","2/") oldDate = replace(oldDate,"March ","3/") oldDate = replace(oldDate,"April ","4/") oldDate = replace(oldDate,"May ","5/") oldDate = replace(oldDate,"June ","6/") oldDate = replace(oldDate,"July ","7/") oldDate = replace(oldDate,"August ","8/") oldDate = replace(oldDate,"September ","9/") oldDate = replace(oldDate,"October ","10/") oldDate = replace(oldDate,"November ","11/") oldDate = replace(oldDate,"December ","12/") '''''''''''''''''''''''''''''''''''''17/6/2010 08:58:02 AM datetemp = split(oldDate,"/") newMonth = datetemp(1) newDay = datetemp(0) stuff = datetemp(2) theNewDate = newMonth & "/" & newDay & "/" & stuff '''''''''''''''''''''''''''''''''''''6/17/2010 08:58:02 AM sql = "UPDATE TABLE1 SET employee_signed_on = '" & theNewDate & "' WHERE id = " & id response.Write sql & "<br>" conn.execute(sql) rs.movenext loop %> Hope this helps. From: Shreyas Agasthya [mailto:shreyasbr(a)gmail.com] Sent: Thursday, June 17, 2010 11:42 AM To: David Stoltz Cc: RQuadling(a)googlemail.com; ash(a)ashleysheridan.co.uk; php-general(a)lists.php.net Subject: Re: [PHP] Date Conversion Problem 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(a)shh.org> 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(a)gmail.com] Sent: Thursday, June 17, 2010 8:47 AM To: David Stoltz Cc: ash(a)ashleysheridan.co.uk; php-general(a)lists.php.net Subject: Re: [PHP] Date Conversion Problem On 17 June 2010 13:40, David Stoltz <Dstoltz(a)shh.org> 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(a)ashleysheridan.co.uk] > Sent: Thursday, June 17, 2010 8:38 AM > To: David Stoltz > Cc: php-general(a)lists.php.net > Subject: Re: [PHP] 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 |