[SOLVED] Query for Disinct Date not with time

[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

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date

[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

 

 

 

 

 

[PFMaBiSmAd]

The index name that is used is literally what is in the select, so it is '(DATE(lot_date))'. However, the extra () seem to be a problem for php to evaluate the index (if you develop and debug this on a system will full php error reporting turned on you will get a php error.)

 

I recommend using an alias (the AS ld in the following) AND distinct is not a function, so it does not use () anyway.

 

$query_rsdate = "select distinct DATE(lot_date) AS ld from auct_lots_full";

 

<option value="<?php echo $row_rsdate['ld'];?>" ><?php echo $row_rsdate['ld'];?></option>

[habib009pk]

Hi,

 

Thanks alot for great help it has been done through usage of Alias

 

Many Many Thanks

 

Regards