manhattes Posted October 30, 2015 Share Posted October 30, 2015 Is there an easy way to change dates in a column so that if the date falls on a Saturday or Sunday it changes to the closest business day? Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/ Share on other sites More sharing options...
0x00 Posted October 30, 2015 Share Posted October 30, 2015 I guess to use an UPDATE using DAYOFWEEK https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek .. UPDATE date=date+(1 day) .. WHERE DATEOFWEEK(date)=1 You'd need to combine two cases, one for Sat and another for Sun, and no +(1 day) isn't real, but would depend on how you're storing the date Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524865 Share on other sites More sharing options...
manhattes Posted October 30, 2015 Author Share Posted October 30, 2015 I guess to use an UPDATE using DAYOFWEEK https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek .. UPDATE date=date+(1 day) .. WHERE DATEOFWEEK(date)=1 You'd need to combine two cases, one for Sat and another for Sun, and no +(1 day) isn't real, but would depend on how you're storing the date So my date is stored as Y-m-d Update Table.date = date + (1 day) WHERE DAYOFWEEK(date)=1 and Update Table.date = date - (1 day) WHERE DAYOFWEEK(date)=7 ? Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524869 Share on other sites More sharing options...
benanamen Posted October 30, 2015 Share Posted October 30, 2015 (edited) OP, are you also needing a solution to convert the dates BEFORE they get into the DB or just update the dates that are already in there? And, do you want to actually change the existing dates or perhaps add a monday_date column or even just calculate Mondays date on output? This will show you a SELECT: SELECT DATE_ADD( '2015-10-24', INTERVAL ( 9 - IF ( DAYOFWEEK('2015-10-24') = 1, 8, DAYOFWEEK('2015-10-24') ) ) DAY ) AS NEXTMONDAY; I will leave it to someone else to show you the update. Edited October 30, 2015 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524870 Share on other sites More sharing options...
manhattes Posted October 30, 2015 Author Share Posted October 30, 2015 OP, are you also needing a solution to convert the dates BEFORE they get into the DB or just update the dates that are already in there? no I have been storing new dates as dates as instructed. :-) Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524872 Share on other sites More sharing options...
0x00 Posted October 30, 2015 Share Posted October 30, 2015 no I have been storing new dates as dates as instructed. :-) You'd be better off doing it pre-storage, even inform the user that the dates are not valid. Storing dates as instructed??? By whom... actually who cares, in what format are they? Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524875 Share on other sites More sharing options...
manhattes Posted October 30, 2015 Author Share Posted October 30, 2015 no I have been storing new dates as dates as instructed. :-) I want to change the dates to either Friday or Monday. Running 2 queries is fine. I dont understand your select. when i change the dates to the column date it doesnt work. Syntax error on Dateadd Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524877 Share on other sites More sharing options...
manhattes Posted October 30, 2015 Author Share Posted October 30, 2015 You'd be better off doing it pre-storage, even inform the user that the dates are not valid. Storing dates as instructed??? By whom... actually who cares, in what format are they? The dates are used for my own purpose and having them fall on weekdays don't help since I dont work on Sat. and Sun. ussually. haha I was scolded once on here for having dates stored in VARCHAR. They are stored as a date and formatted Y-m-d or ex. 2015-10-24 Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524878 Share on other sites More sharing options...
0x00 Posted October 30, 2015 Share Posted October 30, 2015 I told you it wouldn't, I just gave you the concept, Benanamen did though, except the last touch... so have a go and report back with code if it doesn't and someone will help. You won't learn if we just do it for you Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524879 Share on other sites More sharing options...
0x00 Posted October 30, 2015 Share Posted October 30, 2015 BTW I store dates as int's Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524880 Share on other sites More sharing options...
benanamen Posted October 30, 2015 Share Posted October 30, 2015 (edited) I dont understand your select. when i change the dates to the column date it doesnt work. Syntax error on Dateadd The query I posted is not the answer to your question. As posted, we are not going to just do it for you. Learn what the query I gave you is doing and you will be halfway to having your answer. Edited October 30, 2015 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524881 Share on other sites More sharing options...
benanamen Posted October 30, 2015 Share Posted October 30, 2015 (edited) BTW I store dates as int's Why would you do that? * Anyone know why the quotes default to two spaces before your text? And can it be changed without going back and editing the post? Edited October 30, 2015 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524882 Share on other sites More sharing options...
manhattes Posted October 30, 2015 Author Share Posted October 30, 2015 SELECT `BuyDate` FROM `Calendar` DATE_ADD( `BuyDate`, INTERVAL ( 9 - IF ( DAYOFWEEK(`BuyDate`) = 1, 8, DAYOFWEEK(`BuyDate`) ) ) DAY ) AS NEXTMONDAY; #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_ADD( `BuyDate`, INTERVAL ( 9 - IF ( DAYOFWEEK(`BuyDate`) ' at line 2 Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524883 Share on other sites More sharing options...
benanamen Posted October 30, 2015 Share Posted October 30, 2015 (edited) So you are trying. Look at your code closely. Where are you supposed to put FROM? https://dev.mysql.com/doc/refman/5.0/en/select.html Edited October 30, 2015 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524884 Share on other sites More sharing options...
0x00 Posted October 30, 2015 Share Posted October 30, 2015 Why would you do that? * Anyone know why the quotes default to two spaces before your text? And can it be changed without going back and editing the post? A) It uses the same memory as a TIMESTAMP variable and I've always used unixtime, also easily transferable to other formats on different platforms without parsing or formatting. Oh and my CMS allows for user defined output formats, so may as well only do once. B) Probably part of the regex used for BB code parsing? Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524885 Share on other sites More sharing options...
benanamen Posted October 30, 2015 Share Posted October 30, 2015 @0x00, You didnt mention unixtime. Nevermind. Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524886 Share on other sites More sharing options...
Solution Barand Posted October 30, 2015 Solution Share Posted October 30, 2015 try this to correct the dates in the table UPDATE date_sample SET date = CASE WHEN dayofweek(date)=1 THEN date + INTERVAL 1 DAY -- change Sun to Mon WHEN dayofweek(date)=7 THEN date - INTERVAL 1 DAY -- change Sat to Fri ELSE date -- leave weekdays alone END If you want to leave them alone in the table but modify on selection then you can use the same CASE statement SELECT CASE WHEN dayofweek(date)=1 THEN date + INTERVAL 1 DAY -- change Sun to Mon WHEN dayofweek(date)=7 THEN date - INTERVAL 1 DAY -- change Sat to Fri ELSE date -- leave weekdays alone END as date FROM table Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524887 Share on other sites More sharing options...
benanamen Posted October 30, 2015 Share Posted October 30, 2015 (edited) Oh @Barand, I was trying to teach the brutha a little something, something, not hand him the answers. LOL! By the way, he didnt want to change anything to Fridays. He wants Saturdays AND Sundays to be the next Monday. He is going to jack his dates. Edited October 30, 2015 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524892 Share on other sites More sharing options...
manhattes Posted October 30, 2015 Author Share Posted October 30, 2015 Thanks that was so much easier then the others! Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524894 Share on other sites More sharing options...
Barand Posted October 30, 2015 Share Posted October 30, 2015 SELECT DATE_ADD( '2015-10-24', INTERVAL ( 9 - IF ( DAYOFWEEK('2015-10-24') = 1, 8, DAYOFWEEK('2015-10-24') ) ) DAY ) AS NEXTMONDAY;I will leave it to someone else to show you the update. Sorry, I didn't realize that was teaching, it sure confused me. Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524897 Share on other sites More sharing options...
benanamen Posted October 30, 2015 Share Posted October 30, 2015 (edited) it sure confused me I dont believe that. And I don't know enough about the OP's situation to feel comfortable to have him do a mass update on his data because of my say so. I was leading to a solution to give him the dates he wanted on output without changing his data. It must of went in the way it did for some reason. OP's dont always ask the right questions to achieve the ultimate purpose. They ask for an answer to what they "think" they need to do. Still dont know if changing all the dates is the right thing to do. Edited October 30, 2015 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/298940-changing-dates-to-nearest-business-day/#findComment-1524898 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.