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@xxxxxxxxx]
Sent: Thursday, June 17, 2010 11:42 AM
To: David Stoltz
Cc: RQuadling@xxxxxxxxxxxxxx; ash@xxxxxxxxxxxxxxxxxxxx;
php-general@xxxxxxxxxxxxx
Subject: Re: 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@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
