Changing Dates to nearest Business Day

[manhattes]

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?

 

 

[0x00]

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

[manhattes]

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

?

[benanamen]

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

[manhattes]

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. :-)

[0x00]

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?

[manhattes]

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

[manhattes]

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

[0x00]

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

[0x00]

BTW I store dates as int's

[benanamen]

 

 

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

[benanamen]

 

 

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

[manhattes]

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

[benanamen]

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

[0x00]

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?

[benanamen]

@0x00, You didnt mention unixtime. Nevermind.

[Barand]

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

[benanamen]

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

[manhattes]

Thanks that was so much easier then the others!

[Barand]

 

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.

[benanamen]

 

 

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