First date of any month in SQL-CodePudding

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

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;

CodePudding user response：

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);

CodePudding user response：

SELECT table.date,
       table.month_add,
       DATE_FORMAT(table.date   INTERVAL table.month_add MONTH, 
                   '%Y-%m-01') AS beginning_of_month
FROM table

CodePudding user response：

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.