Page 1 of 1
Query for Disinct Date not with time
Posted: Fri Jul 31, 2009 1:25 am
by habib009pk
Hi Friends,
I have a little problem, i want to get the distinct Date with only date, month and year without time. But when i am using that query:
"SELECT DISTINCT(lot_date) from auct_lots_full ORDER BY lot_date ASC";
It will give me distinct date but with time also because the time is also saved in my database.
e.g i have values
2009-07-31 00:00:00
2009-07-31 09:58:00
2009-07-31 09:59:00
2009-07-31 09:59:00
2009-07-31 10:00:00
2009-07-31 10:00:00
and my query give me the result
2009-07-31 00:00:00
2009-07-31 09:58:00
2009-07-31 09:59:00
2009-07-31 10:00:00
but i want the result
2009-07-31 (just distinct Date not with time)
Please give me the query.
Thanks and Regards
Re: Query for Disinct Date not with time
Posted: Fri Jul 31, 2009 2:22 am
by VladSun
This is what you need:
http://dev.mysql.com/doc/refman/5.1/en/ ... ction_date
Take a look at all of the functions listed there

Re: Query for Disinct Date not with time
Posted: Fri Jul 31, 2009 11:25 pm
by habib009pk
Hi,
Thanks for helping me, from your given function i am using that code for my desired result
//Query to Get the Date from the table
mysql_select_db($database_kansai, $kansai);
$query_rsdate = "select distinct(DATE(lot_date)) from auct_lots_full";
$rsdate = mysql_query($query_rsdate, $kansai) or die(mysql_error());
$row_rsdate = mysql_fetch_assoc($rsdate);
echo $totalRows_rsdate = mysql_num_rows($rsdate); //it will give me the no of records 6 as i have the data of 6 days in my database.
Now after that for retrieval of data i am using using that code
<form action="japan_auction_manage.php" method="post" name="dateform" id="dateform">
<select name="date" id="date">
<option value="">Any</option>
<?php
do {
?>
<option value="<?php echo $row_rsdate['DATE(lot_date)'];?>" ><?php echo $row_rsdate['DATE(lot_date)'];?></option>
<?php
} while ($row_rsdate= mysql_fetch_assoc($rsdate));
?>
</select>
</form>
Now the problem is that as the above query gave 6 records so loop will be excuting 6 times
but no value has been printed in the option area.
So please help me what code has been written in the bold area of the select list object. so we can see the date in the option portion.
Thanks and Regards
Re: Query for Disinct Date not with time
Posted: Fri Jul 31, 2009 11:42 pm
by manojsemwal1
in your query when u insert the date that time u just enter the date only like $date = date("d/m/y"); this method.
it will insert only date after that you can call your distinct query and when u want to display the result just use.
$AddDate=mysql_result($rs,$n,12);
$dateTime = new DateTime($AddDate);
$formatted_date=date_format ($dateTime, 'd-m-Y' );
the $formatted_date will be date month and year
i think it will help u
regards
Manoj
Re: Query for Disinct Date not with time
Posted: Sat Aug 01, 2009 12:32 am
by habib009pk
Hi,
Thanks of Help but is has been done and my result is perfect now
Regards