Question 113128 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – First date of any month in SQL

I am seeking to find first date of the month in the corresponding table:

enter image description here

So if i have 26/08/2011 August as date and 2 months to add, it becomes 26/10/2011. I need the first date of the resulting month- like 01/10/2011.

Can this be achieved in SQL?

Update : I could get the date of the month using
DATEADD(month,months_add, date)

Couldnt get to "beginning of month". Tried: How can I select the first day of a month in SQL?
But for me it throws the error: function pg_catalog.date_diff("unknown", integer, date) does not exist;

Tags:

3

Answers


  1. 0

    You could try using date_add for add two months and date_sub for sub the days -1

    set @my_date = "2017-06-15";
SELECT DATE_SUB( DATE_ADD(@my_date, INTERVAL 2 MONTH), 
       INTERVAL DAYOFMONTH(@my_date)-1 DAY);
    Login or Signup to reply.
  2. 0 
    SELECT table.date,
       table.month_add,
       DATE_FORMAT(table.date + INTERVAL table.month_add MONTH, 
                   '%Y-%m-01') AS beginning_of_month
FROM table
    Login or Signup to reply.
  3. 0

    Assuming your date is currently a varchar in dd/MM/yyyy format, you can use STR_TO_DATE to convert it to a DATE type column, then use DATE_ADD with your months_add column to dynamically add months then finally use DATE_FORMAT to display it back in a 01/MM/yyyy format with the first day of the month.

    SELECT 
  Date_Column, 
  Date_Months_Add, 
  DATE_FORMAT(DATE_ADD(STR_TO_DATE(Date_Column, "%d/%m/%Y" ), INTERVAL Date_Months_Add MONTH), '01/%m/%Y') AS Date_Beginning
FROM sample

    Result:

    | Date_Column | Date_Months_Add | Date_Beginning  |
|-------------|-----------------|-----------------|
| 26/08/2011  | 2               | 01/10/2011      |
| 25/04/2011  | 1               | 01/05/2011      |
| 16/09/2022  | 3               | 01/12/2022      |
| 14/07/2022  | 4               | 01/11/2022      |

    Fiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search