Date Conversion Problem
in [General]
|
Prev: Unit testing in PHP
Next: curl help or other suggestion
From: "David Stoltz" on 17 Jun 2010 08:35 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!
From: Ashley Sheridan on 17 Jun 2010 08:37 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
From: "David Stoltz" on 17 Jun 2010 08:40 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
From: Richard Quadling on 17 Jun 2010 08:40 On 17 June 2010 13:35, David Stoltz <Dstoltz(a)shh.org> 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! > > Under normal conditions, you would store the date in a datetime column. That allows you to do all the date range work in the DB. When you display the date, you would use PHP's date() function for format it appropriately. date('l jS \of F Y h:i:s A', $row['fieldname']); sort of thing. -- ----- 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: Richard Quadling on 17 Jun 2010 08:44
On 17 June 2010 13:40, Richard Quadling <rquadling(a)gmail.com> wrote: > On 17 June 2010 13:35, David Stoltz <Dstoltz(a)shh.org> 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! >> >> > > Under normal conditions, you would store the date in a datetime > column. That allows you to do all the date range work in the DB. > > When you display the date, you would use PHP's date() function for > format it appropriately. > > > date('l jS \of F Y h:i:s A', $row['fieldname']); > > sort of thing. > > -- > ----- > 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 > Having said that, you will have some serious issues is your dates are generated from around the world and not purely in your local timezone. A lack of timezone (Europe/London, Europe/Berlin) rather than the timezone offset (+1:00, etc.) is the issue here. Due to DST changes not being consistent worldwide, with the timezones changing over time, etc. All quite complicated. -- ----- 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 |