Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT DATE_ADD(`date_field`, INTERVAL 5 DAY) As FinalDate
- FROM `table_name`;
- SELECT DATE_ADD(
- date_field,
- INTERVAL 5 +
- IF(
- (WEEK(date_field) <> WEEK(DATE_ADD(date_field, INTERVAL 5 DAY)))
- OR (WEEKDAY(DATE_ADD(date_field, INTERVAL 5 DAY)) IN (5, 6)),
- 2,
- 0)
- DAY
- ) AS FinalDate
- FROM `table_name`;
- SELECT
- @ID:='2017-10-04' as initial_date, -- the initial date in the right format to manipulate (add x day)
- @DTA:=20 as days_to_add, -- number of days to add
- @DA:= DATE_ADD(@ID, INTERVAL @DTA DAY) as date_add,
- @LASTDAY := WEEKDAY(@DA) as last_day, -- the day (Monday, Tuesday...) corresponding to the initial date + number of days to add
- @WEEK1 := DATE_FORMAT(@ID, '%v') as initial_date_week, -- format the initial date to match week mode 3 (Monday 1-53)
- @WEEK2 := DATE_FORMAT(@DA, '%v') as added_date_week_nbr, -- the week # of the initial_date + number of days to add
- @WEEKDIFF := @WEEK2 - @WEEK1 as week_difference, -- the difference between week 2 and week 1
- DATE_ADD(@ID,
- INTERVAL @DTA +
- if ( @WEEKDIFF > 0 or @LASTDAY in (5,6),
- 2,
- 0
- ) +
- if (@WEEKDIFF > 1,
- @WEEKDIFF*2,
- 0
- ) DAY
- ) AS FinalDate
Add Comment
Please, Sign In to add comment