Using a specific date of the month

[jadelantern]

Hi guys, i was having an issue with dates and although it leans a bit more into MYSQL I was hoping i could get some help ive searched all over the net for this so I dont know if im not thinking on how to ask this correctly or can't find it, but ill be as detailed as possible and hope that helps.

 

Ok I have a table that shows when payment was received on past invoices dating back to say 1995;  The company processes 'ALL' invoices every month on the 9th, so if anything comes in from the first to the ninth it will be processed that month on the ninth.  If it comes in from the tenth to the thirtieth or thirty-first it will be processed the following month.

 

The table already has a "Received" date filed and now I want to create a column on the table called "Processed Date" (adding the column is no problem don't need help with that part) that shows me the date we processed the invoice.

 

I know i have to use the "Received" field (because it shows what day the invoice was put into the system) but im not sure how to format the "Processed Date" field, so say the "Received" field shows an invoice came in on  '1995-05-11', I want the "Processed Date" field to display it was payed on '1995-06-09' (notice that is the following month).

 

I thought i would put in a visual of what I was trying to do below:

 

So if I pull the two columns in MYSQL from that table I would see something like this:

 

Received                Processed Date

1996-02-03            1996-02-09

1996-03-03            1996-03-09

1996-04-11            1996-05-09

1996-05-20            1996-06-09

1996-07-13            1996-08-09

 

If i could get some guidance/help on this i would really appreciate it.

 

thanks in advance!!

 

JL

[Jacques1]

Well, just write down how exactly you derive the Processed Date “1996-02-09” from the Received Date “1996-02-03”. The year is always the same, the day is always the 9th. And the month? If ...

 

In fact, what's the point of physically storing the Processed Date when you can calculate it ad hoc?

[jadelantern]

Hi Jacques1 thank you for responding.

 

Im not sure what you mean, but no the year won't always be the same, (say for instance the invoce came/comes in on 1996-12-22 then it wouldn't be processed till 1997-01-09). Plus I don't have the option to just write it down as I was asked to do this and the information will be in the table.

 

thank you!

 

JL

Edited September 17, 2014 by jadelantern

[Jacques1]

I'm saying that you need to sit down with a pencil and a piece of paper and write down the procedure for getting from one date to another. That's how you solve problems: You start with a vague idea, you come up with a concrete solution, and then you implement the solution. If you cannot do the solution in your head, write it down.

 

I'm trying to get you from the first stage (the vague idea) to the second (the concrete solution). So again: How exactly do you get from one date to the other?

 

 

 

the year won't always be the same

 

From our perspective, it is. MySQL will take care of jumping to the next year if necessary.

[Psycho]

If you are going to build logic to 'calculate' the process date, then you don't need to save it in the database and instead use that calculation when needed. But, I could see where some things may be easier to accomplish if you have that value saved. So, I will not tell you to do it one way or the other, but I will show you how to get the date in question.

 

The logic could look something like this:

SELECT
    -- If the Day of received date is <= 9
    IF(DAY(received_date) <= 9,
    -- Then Use 9th of current month
       DATE_FORMAT(received_date, '%Y-%m-09'),
    -- Else use 9th of next month
       DATE_FORMAT(DATE_ADD(received_date, INTERVAL 1 MONTH), '%Y-%m-09')
      ) as processed_date

Basically we test the "day" of the received date. If it is less than 9 (1-9) then we determine the processed date tot he 9th of the given month. Otherwise we determine it as the 9th of the next month.

 

An update query to set a value in the new column might look like this

UPDATE `table`
SET processed_date = 
    -- If the Day of received date is <= 9 
    IF(DAY(received_date) <= 9,
    -- Then Use 9th of current month
       DATE_FORMAT(received_date, '%Y-%m-09'),
    -- Else use 9th of next month
       DATE_FORMAT(DATE_ADD(received_date, INTERVAL 1 MONTH), '%Y-%m-09')
      )

Edited September 17, 2014 by Psycho

[Jacques1]

Psycho, it's incredibly frustrating that whenever I try to explain something and get people to think for themselves, you or one of your buddies jumps in and posts the solution.

 

Do you know the saying about teaching a man to fish? You've spent many years handing out free fish, now it might be the time to teach.

[jadelantern]

@Jacques1  I understand what you were trying to do but to be honest I had already written it down and had worked on it for a long while. The reason I came here for help was I was already past that stage of trying to figure it out on my own an needed help from someone else so I don't/didn't really need a life lesson but thank you anyway.

 

@Psycho THANK YOU SOOOO MUCH, i had been working on this for a while and wasn't able to get it and you knocked it out of the park!! THANK YOU THANK YOU THANK YOU!!!

[Jacques1]

Have you guys ever wondered why PHP is generally regarded as a toy language for kiddies and gets as much respect as a fart in the elevator? I think now you know why.

[jadelantern]

So let me see if I understand this.......

 

PHP is is thought of as a toy language for kiddies because a guy working in MYSQL who already worked on something for a few hours asked for help on a project in MYSQL that never touches PHP.....

 

well... that escalated quickly!

 

Sorry I have just flushed the entire PHP language for all of you by asking for some MYSQL help Ill try not to ask any questions about javascript so as not to ruin Linux for anyone.....

 

thanks and sorry all 

 

JL

[jazzman1]

 

Psycho, it's incredibly frustrating that whenever I try to explain something and get people to think for themselves, you or one of your buddies jumps in and posts the solution

@Jacques, I had lived in a country for more than 38 years where the communist party said what's wrong and what's not.This is a free php helping forum, here is not a school, nor we are teachers. If you have some personal issues with someone from the "staff", so be free to PM'ed him. There is no sense to make public your complaints.

Edited September 18, 2014 by jazzman1