Selecting part of date field

MitchEvans

Hi Guys,

How do I select just the first part of the date field:

My date is stored like so 1/9/2008 & 27/4/08
So the code below only picks up the first number, but if I get it to pick up two numbers and the date is like the first so 1/9/2008 it picks up "1/" which wouldn't work.

$stats = "SELECT * FROM contactus WHERE month LIKE $month AND substring(date,1,1) BETWEEN '$from' AND '$to'";

It thinks that a date like 10/9/2008 is a 1 not a 10

So I want everything left of the first "/"

Hope this makes sense and someone can help me here,

Thanks!

NogDog

Is this a DATE or DATETIME column, or some sort of character column (VARCHAR, etc.)?

bradgrafelman

And before you give us the answer (character column), also tell us why you wouldn't store a date value in a DATE column type (which would make this query much easier and more efficient).

MitchEvans

Yes you would be right as unfortunately I didn't set up the tabel luckily I have full acces to the tables now, so what would make it easier for me to do what I want to do then?

So how would i select everything left of thre first "/"

bradgrafelman

MitchEvans wrote:
so what would make it easier for me to do what I want to do then?

Convert the column to a DATE column:

Add a new column of type DATE, give it a temporary name like date_temp.

UPDATE myTable SET date_temp = STR_TO_DATE(`date`, '%e/%c/%Y')

Drop the date column and rename date_temp to take its place.

Note that I assumed the format you were previously storing dates as was d/m/yyyy.

EDIT: Forgot to explain further... after you convert the column to a proper DATE type, you'd simply use MONTH(date) in your queries to select only the month part of the date. You can also use the DATE_FORMAT() function to format the date however you'd like.

MitchEvans

Hi,

Just been away for a while thanks for getting back!

I'm unsure as to how this is to work, i've just run it in php and can't seem to get it to work there:

$directory = "SELECT * FROM contactus";
$directoryResults = mysql_query($directory);
while ($directoryRow = mysql_fetch_array ($directoryResults)){
$date = $directoryRow["date"];

$date1=str_to_date('$date', '%e/%c/%Y');

echo"$date1<br>";

It returns nothing

Hope you can help