MYSQL Add working days to date
I want to add 5 days to the provided date, but the calculation must skip weekends.
I already know how to add 5 days without skipping weekends:
SELECT DATE_ADD(`date_field`, INTERVAL 5 DAY) As FinalDate FROM `table_name`;
Now I want the returned value to skip weekends.
date_field = 2016-07-22 the results will be
But I want the results to be
MySQL Function to add a number of working days to a DATETIME , This is new function with mysql syntax: DROP FUNCTION IF EXISTS AddWorkDays; DELIMITER $$ CREATE FUNCTION AddWorkDays ( WorkingDays INT, 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`; How it works: Firstly, it will add 5 days on your date.
I did try your solution but faced a problem when using it with a larger interval (e.g 20 days). It works perfectly with little intervals though.
Example : for '2017-10-04' + 20 days, your algorithm return '2017-10-26'. It should be '2017-11-01' since we skip 4 weekends.
The numbers of days you add isn't calculated depending on the difference between the 2 week #, so the maximum days you can add is 2 and in my case, it should be 8 (4x2).
I modified your code to end up with this (I also add variables, much more convenient to modify)
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
The way I get my week numbers can seems weird but this is because I'm running this in France and my DB seems to be configured in a way that weeks are natively starting by Sunday, "%v" represent the 'mode 3' for weeks, you can check the MySQL documentation here for more details : https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html (ctrl + F > '%v')
I didn't implement the public holiday yet, but I'm thinking of adding X days in the calculation each times one of this day is in the period we're looking at.
According to my (few) tests, this should work. Let me know if not
MySQL DATE_ADD() Function, Example. Add 10 days to a date and return the date: SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);. Try it Yourself » The value of the time/date interval to add. Both positive and negative values are allowed: addunit: Required. The type of interval to add. Can be one of the following values: MICROSECOND; SECOND; MINUTE; HOUR; DAY; WEEK; MONTH; QUARTER; YEAR; SECOND_MICROSECOND; MINUTE_MICROSECOND; MINUTE_SECOND; HOUR_MICROSECOND; HOUR_SECOND; HOUR_MINUTE; DAY_MICROSECOND; DAY_SECOND
try this out, should work nicely, basically loop through each of the days and check if they are saturday or sunday, ignore them.
MySQL: Using DATE_SUB to subtract business days instead of all , mysql. I am looking for a robust solution to subtract the number of business days from a date. I am using DATE_SUB however it subtracts all calendar days. i’m new to sql and i’m using mysql workbench to generate data mart. i need help..i want to generate dates (e.g. 2005-01-01), day of week ( 1 to 7), day of month (1 to 31), day name and month name for the whole of year 2005. the primary key is the date_key which starts from 1 to 365 and i already upload the date key into my database. it just
Plus hollydays 1 or 2
select GREATEST(WEEKDAY(NOW()) - 4, 0) 'hollydays'
MYSQL Add working days to date, oracle add business days to date mysql business days sql subtract business days mysql date add how to calculate business days between two dates in oracle Fill in the diagonal from top left to bottom right with all 0 (i.e. there are 0 working days between Monday and Monday, Tuesday and Tuesday, etc.). For each day start at the diagonal (must always be 0) and fill in the columns to the right, one day at a time.
WHERE datefield BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY AND WEEKDAY(datefield) NOT IN (5,6);
Recursively use a function to exclude Weekends and , I a written a UDF and a stored procedure to calculate the due date after excluding filtering the Weekends and the public holidays to find the working due date. corresponding to the initial date + number of days to add SET So if the timeframe is longer than a working week (five days) you can: Find the number of weeks by taking the floor of the N / 5. Multiply it by seven to give the number of weeks spanned. Move the start date to the next Monday, then add the calculated weeks. Loop from this point for another ( N
Add Workdays or Selling Days - Feature Requests, I hope that it will become easier to determine the total # of days in… analytics, one time format that is important to us is selling days or business days in a given month. current date references with a date/timestamp column to get days in that particular month). /*MySQL */ select curdate() as today , last_day(curdate()) as day, dd, d = Day; week, ww, wk = Week; weekday, dw, w = Weekday; hour, hh = hour; minute, mi, n = Minute; second, ss, s = Second; millisecond, ms = Millisecond; number: Required. The number of interval to add to date. Can be positive (to get dates in the future) or negative (to get dates in the past) date: Required. The date that will be modified
MySQL add days to a date?, To add days to a date, you can use DATE_ADD() function from MySQL. The syntax is as follows to add days to a date −INSERT INTO Date Calculator: Add to or Subtract From a Date Enter a start date and add or subtract any number of days, months, or years. Count Days Add Days Workdays Add Workdays Weekday Week №
MySQL function to find the number of working days between two , calculates the number of business days between the start date @S and CREATE TABLE Calendar ( calendar_date DATETIME NOT NULL, In this article we will discuss on How to add Days, Weeks, Months, Quarters or Years to a Date in Sql Server? You may also like to read the following other popular articles on Date and Time in Sql Server: We can use DATEADD () function like below to add days to DateTime in Sql Server. DATEADD () functions first parameter value can be day or dd