List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 30 2001 2:35am
Subject:Re: Nesting DATE_FORMAT
View as plain text  
At 12:21 PM +1000 5/30/01, Steven Wren wrote:
>Hello
>
>I am trying to display a table depending if the registration date is
>between a certain period.  To do this , I want to pull the 'day' field
>from another table and construct one of the comparison dates with this
>value.  It is probably best explained with a query.
>
>This is what I am trying to do:
>
>SELECT whatever FROM tbl1, tbl2
>WHERE ( tbl1.registration_date >=
>"DATE_FORMAT(now(),'%Y-%m-)DATE_FORMAT(tbl2.registration_date,'%d')"
>AND tbl1.registration_date >= DATE_FORMAT(now(),'%Y-%m-%d') ) AND
>tbl2.id=1000
>
>So both tables have their own "registration_date",
>and I am trying to display rows when the registration_date of tbl1 is
>between the day of tbl2 joined with todays month and year AND todays date.

CONCAT(YEAR(CURRENT_DATE),'-',MONTH(CURRENT_DATE),'-',DAYOFMONTH(tbl2.registration_date))


>
>e.g.
>
>if the reg_date in tbl2 is 2000-12-15 and todays date is 2001-05-29, it
>should display any records of tbl1 that have a registration date between
>2001-05-15 and 2001-05-29
>
>This is for a payment system, where accounts are paid on the registration
>date as defined in tbl2 each month, and they can check what accounts they
>will be getting paid for during the month.
>
>I am doing this in a PHP script and can pull the day out first then pass
>it in a second query.  Was trying to find a way to do it in one query.
>
>Any tips or hints would be extremely appreciated.
>
>Thanks!
>
>Have a good one.
>
>Regards,
>
>Steven Wren


-- 
Paul DuBois, paul@stripped
Thread
Nesting DATE_FORMATSteven Wren30 May
Re: Nesting DATE_FORMATPaul DuBois30 May