First date of any month in SQL

Question

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;

checkout the date functions of sql: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html (first hit on google ;) ) Tell us what you tried so far and if there are still problems — helle, Oct 18 '22 at 14:32

score 1 · Answer 1 · answered Oct 18 '22 at 14:36

1

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

answered Oct 18 '22 at 14:36

ScaisEdge

131,976
10
91
107

Wrong. `set @my_date = "2017-12-31";` => `'2018-01-29'`. – Akina Oct 18 '22 at 15:04
First truncate the date to the first of the month and then add the requested number of months – Robert Kock Oct 18 '22 at 15:34

score 1 · Answer 2 · answered Oct 18 '22 at 15:03

1

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

answered Oct 18 '22 at 15:03

Akina

39,301
5
14
25

doesnt work :(. – user2458552 Oct 18 '22 at 15:18
@user2458552 Provide source data which produces incorrect output. – Akina Oct 18 '22 at 15:29

score 0 · Answer 3 · answered Oct 18 '22 at 15:22

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.

First date of any month in SQL

3 Answers3