List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:October 17 2008 9:30pm
Subject:Re: FLOOR(DATE_ADD(CURDATE(), INTERVAL 4 MONTH)) -- ?
View as plain text  
On Fri, Oct 17, 2008 at 2:19 PM, Rob Wultsch <wultsch@stripped> wrote:

>
> On Fri, Oct 17, 2008 at 2:15 PM, Ian Christian <pookey@stripped>wrote:
>
>> 2008/10/17 Rob Wultsch <wultsch@stripped>:
>> > On Fri, Oct 17, 2008 at 1:33 PM, Rene Fournier <m5@stripped>
>> wrote:
>> >
>> >> Okay, I realize that query won't work, but that's essentially want I
>> want
>> >> to do:
>> >>
>> >> Add four months to the current date, then return the first day of that
>> >> month, e.g.:
>> >>
>> >>        floor ( 2008-10-16 + 4 months ) = 2009-02-1
>> >>
>> >> Is there a nice SQL way of achieving this?
>> >>
>> >> ...Rene
>> >>
>> >
>> > SELECT date(now() + INTERVAL 4 MONTH - INTERVAL day(now())-1 DAY);
>>
>> Be aware that only 1 suggested answer was correct :)
>>
>> mysql> SELECT date( date("2008-10-31") + INTERVAL 4 MONTH - INTERVAL
>> day("2008-10-31")-1 DAY) as d\G
>> *************************** 1. row ***************************
>> d: 2009-01-29
>>
>> mysql> select cast(date_format( date("2008-10-31") + interval 4
>> month,"%Y-%m-01") as date) as d \G
>> *************************** 1. row ***************************
>> d: 2009-02-01
>>
>
> I should have said:
SELECT DATE( NOW() )-INTERVAL day( NOW() )-1 DAY + INTERVAL 4 MONTH;

but yeah, the date_format is a heck of a lot cleaner. Learn something new
everyday...

-- 
Rob Wultsch

Thread
FLOOR(DATE_ADD(CURDATE(), INTERVAL 4 MONTH)) -- ?Rene Fournier17 Oct
  • Re: FLOOR(DATE_ADD(CURDATE(), INTERVAL 4 MONTH)) -- ?Rob Wultsch17 Oct
    • Re: FLOOR(DATE_ADD(CURDATE(), INTERVAL 4 MONTH)) -- ?Ian Christian17 Oct
      • Re: FLOOR(DATE_ADD(CURDATE(), INTERVAL 4 MONTH)) -- ?Rob Wultsch17 Oct
        • Re: FLOOR(DATE_ADD(CURDATE(), INTERVAL 4 MONTH)) -- ?Rob Wultsch17 Oct
  • Re: FLOOR(DATE_ADD(CURDATE(), INTERVAL 4 MONTH)) -- ?Dan Nelson17 Oct