Jump to content

Changing Dates to nearest Business Day


manhattes

Recommended Posts

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

?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

 

 

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
Link to comment
Share on other sites

 

 

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.