search a SUBSTRING of table entry

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

search a SUBSTRING of table entry

Post by deejay »

Hi

what I want to do is retrieve some information from my database matching the first 10 digits of the field .

ie i have a date in this format '2008-05-14' and in the database it's as such '2008-05-14 23:38:37'


i though that something like this would work

Code: Select all

 
$selectquery = "SELECT * FROM mynews WHERE SUBSTRING('newsdate',0,9) = '$todaysDate' ";
 
 
but doesn't seem to work.


Thanks in advance for any help.
User avatar
freeformer
Forum Newbie
Posts: 14
Joined: Tue May 13, 2008 1:54 pm
Location: UK

Re: search a SUBSTRING of table entry

Post by freeformer »

If that is the exact SQL you are using, then it won't work as you've quoted the field name to use, and so it will be treated as a string.

Also, MySQL's SUBSTRING function starts at offset 1 (in contrast to PHP where it starts at 0).

So, what you'd actually need to use is something like:

Code: Select all

$selectquery = "SELECT * FROM mynews WHERE SUBSTRING(newsdate, 1, 10) = '$todaysDate' ";
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: search a SUBSTRING of table entry

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Re: search a SUBSTRING of table entry

Post by deejay »

cheers guys

:) sorted that now.
Post Reply