DATE_ADD issue in a query

Databases
1

Hi,

I am querying a table (woohoo).

trouble is, I need to get a date+1 and the way I have been trying to do it doesn’t work.

how can I retrieve the start_date +1 so it becomes the next date eg 2010-11-30 getting incremented to 2010-12-01


select 
          bh.hours_type
        , DATE_SUB(bh.start_date, INTERVAL + 1)
        , bh.end_date
        , bhd.day_of_week
        , bhd.shift_number
        , bhd.opening
        , bhd.opening_or_closing
  FROM business_hours AS bh
INNER
   JOIN business_hours_data AS bhd						
      on bhd.hours_id = bh.hours_id

bazz

2

Actually, I must be too tired…

i need to perform the date add on the insert.



insert into business_hours
          ( hours_id, business_id, hours_type, start_date, end_date)
          values ( NULL , ?, ?, ?, ? )
           on duplicate key update
                      business_id = 111
                    , hours_type = 'standard'
                    , start_date = DATE_ADD($end_date, INTERVAL + 1)
                    , end_date = $db_end_date

I’m hoping this is a MySQL thing rather than a perl placeholders issue.

bazz

3

The format of DATE_ADD is

DATE_ADD(date,INTERVAL expr unit),

thus you want

DATE_ADD($end_date, INTERVAL 1 DAY)

4

Thanks very much Philip.

Now I can sleepzzzzzzzz.

bazz

5

why bother with DATE_ADD? just add the interval…

$end_date + INTERVAL 1 DAY