mysql: how to create a date column from another column

Question

I need a query that retrieves a column which type is a date, but based on this column I also need to create another column in my query which takes year and month of this mentioned column, and as day use 1.

This is what I have:

SELECT 
    forecast_id,
    name,
    property_id,
    property_name,
    DATE(YEAR(last_day_of_month), MONTH(last_day_of_month), 1) AS forecast_month,
    last_day_of_month,
FROM table_name;

I am getting an error, and I have no idea how I can get this column which takes the same year and month of one column that exist in my table, and just changes the day.

The error I am getting is:

Error Code: 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 ' MONTH(last_day_of_month), 1) AS forecast_month, last_day_of_month, COUN' at line 6 0.00075 sec

well, you could start by giving us the error you get?

Jan
– Jan

2017-05-25 18:09:08 +00:00
Commented May 25, 2017 at 18:09 — Jan
– Jan, Commented May 25, 2017 at 18:09
@Jan, I just updated my question

lmiguelvargasf
– lmiguelvargasf

2017-05-25 18:10:39 +00:00
Commented May 25, 2017 at 18:10 — lmiguelvargasf
– lmiguelvargasf, Commented May 25, 2017 at 18:10

Jan · Accepted Answer · 2017-05-25 18:28:10Z

1

DATE in MySQL has no three-argument version - thus the error.

You should be able to do this:

SELECT STR_TO_DATE(
   CONCAT(YEAR(last_day_of_month),'-',MONTH(last_day_of_month),'-',1)
    , '%Y-%m-%d') as firstOfMonth 
 FROM table_name;

Another apporach would be to first go to the last day of that month (that's already in last_day_o_month?) , then add one day (1.st of next month) and then go back one month:

 SELECT  date_add(date_add(last_day_of_month, interval 1 DAY),interval -1 MONTH)

edited May 25, 2017 at 18:28

answered May 25, 2017 at 18:18

Jan

13.9k3 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

lmiguelvargasf Over a year ago

this works properly, what about of performance? I see the time increases a lot.

Jan Over a year ago

can you specify "a lot" - how did you measure? How many rows / how frequent your selection are we talking about? Of cause this is a bit backwards for first making a string and then parsing it again...

lmiguelvargasf Over a year ago

so without this column, the duration/fetch time is 0.0078 sec / 0.000024, and when adding your answer 0.0081 / 0.000031.

Jan Over a year ago

So 0.3ms added? Hmmm. You see my second solution?

lmiguelvargasf Over a year ago

thanks for providing a second option to this question.

Gordon Linoff · Accepted Answer · 2017-05-26 22:06:32Z

0

The simplest way is probably to just subtract the day of the month:

select last_day_of_month + interval (1 - day(last_day_of_month)) day

Note: You need to convert to a date if there could be a time component.

edited May 26, 2017 at 22:06

answered May 25, 2017 at 18:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

lmiguelvargasf Over a year ago

this works, but how can I set day to 1, it is like this is subtracting 1 day to the last_day_of_month

lmiguelvargasf Over a year ago

your answer is really good in performance, just the fact of the day is missing to solve my question, and there are no time component involved.

Gordon Linoff Over a year ago

@lmiguelvargasf . . . This sets the day of the month to "1".

lmiguelvargasf Over a year ago

no it doesn't. let's say for value 2017-03-31 in last_day_of_month the value I am getting is 2017-04-30.

Collectives™ on Stack Overflow

mysql: how to create a date column from another column

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related