SELECT DAY(Date)

[roycrom]

I am writing a php page using php 4.3.4 and mysql-4.0.16.

Here is my code:

if (ISSET($THEDAY)) {
    echo "You have chosen the date $THEDAY/$THEMONTH/$THEYEAR";
} else {
    $result = mysql_query("SELECT DAY(Date) FROM database WHERE YEAR(Date)='$THEYEAR' AND MONTH(Date)='$THEMONTH'"); ?>
    <FORM METHOD=GET>
    <SELECT NAME=&quot;THEDAY&quot;> <?
    while ( $row = mysql_fetch_array($result)) {
        echo &quot;<OPTION>$row[0]</OPTION>&quot;;
    } ?>
    </SELECT>
    <INPUT TYPE=HIDDEN VALUE=&quot;<? echo $THEYEAR; ?>&quot; NAME=&quot;THEYEAR&quot;>
    <INPUT TYPE=HIDDEN VALUE=&quot;<? echo $THEMONTH; ?>&quot; NAME=&quot;THEMONTH&quot;>
    <INPUT TYPE=SUBMIT VALUE=&quot;GO&quot;>
    </FORM> <?
}

I can get the month out by doing

SELECT MONTH(Date) FROM database WHERE YEAR(Date)='$THEYEAR';

so the problem is where i say

SELECT DAY(Date)"

Is there a way of selecting the DAY part of the date?

From the docs it looks as though just the MONTH and YEAR can be got this way.





------------------------------------------
Somethings come from nothing, nothing seems to come from somethings - SFA - Guerilla

roycrom

 

[sleipnir214]

It may be your version of MySQL. The MySQL online manual states that the day function is available in versions of MySQL newer than 4.1.1.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[roycrom]

That release isn't the production release yet and I'm in a production environment.

Im sure there must be a way to do it. I dont want to go down the explode route though. One more question,

If the Month is from Jan to September then it gives it as 1 through to 9. Do you know of anyway to pad it out so it gives 01 through 09?

Thanks for your reply

------------------------------------------
Somethings come from nothing, nothing seems to come from somethings - SFA - Guerilla

roycrom

 

[sleipnir214]

On the MySQL side:

SELECT LEFT(CONCAT('0',DAY(columname)),2) FROM tablename.


On the PHP side, I'd use printf() to leftpad zeroes to the desired length.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[r937]

[tt]select `Date`
, month( `Date` ) as m
, extract( DAY from `Date` ) as d
from `database` [/tt]

backticks around names that just might be reserved words

rudy

http://r937.com/

SQL Consulting

 

[roycrom]

Thanks for the replies, I had already implemented a different idea by thhe time I read them though.

I know its only a work around but it works well.

if (str_len($THEMONTH) == 1) {
    $THEMONTH = 0$THEMONTH;
}

DODGY!! it works like a dream

Thanks again for the replies.


------------------------------------------
Somethings come from nothing, nothing seems to come from somethings - SFA - Guerilla

roycrom