How to add first day and last day of month in between query in sql?

Question

I have a query like this.

SELECT IFNULL(SUM(price),0) as total FROM table1
WHERE table1.id= 33 AND start_time BETWEEN '2019-09-1' AND '2019-09-11'

This works fine. Now I want to SUM the data of a month so I tried to use the between feature and get first day and last day of the month.

SELECT IFNULL(SUM(price),0) as total FROM table1 
WHERE table1.id = 33 AND start_time BETWEEN (SELECT DATEADD(month, DATEDIFF(month, 
'2019-09-15'), 0) AND '2019-09-11'

I used this ref

Are you using MySQL or SQL Server? Your code has SQL Server functions. — Gordon Linoff, Oct 17 '19 at 18:57
What is `month` supposed to be? Is it a column in the table? — Barmar, Oct 17 '19 at 18:58
@Barmar It might be the query looks like the same but my issue and query is different. :) — Sachin Shah, Oct 17 '19 at 19:07
Right now the the date is static after that I'll make it dynamic. I have a date like `2019-10-15`. From this date I want to get first date and last date of the month. — Sachin Shah, Oct 17 '19 at 19:09

score 2 · Accepted Answer · answered Oct 17 '19 at 19:33

2

You can get last_day() function without any argument to get the last day of month, and date_format(@mydate, '%Y-%m-01') to get the first day of the month. So, use :

set @mydate='2019-09-15';

select ifnull(sum(price),0) as total 
  from table1 
 where id = 33 
   and start_time between date_format(@mydate, '%Y-%m-01') and last_day(@mydate);

answered Oct 17 '19 at 19:33

Barbaros Özhan

59,113
10
31
55

Hey I just perform the same query. :) – Sachin Shah Oct 17 '19 at 19:34
nice coincidence :) @SachinShah – Barbaros Özhan Oct 17 '19 at 19:37

Gordon Linoff · Answer 2 · 2019-10-17T19:12:17.767

0

You can calculate the first day of the month by subtracting the day of the month and adding one day:

SELECT IFNULL(SUM(price), 0) as total
FROM table1 
WHERE table1.id = 33 AND
      start_time >= date('2019-09-15') + interval (1 - day(date('2019-09-15'))) day and
      start_time < date('2019-09-15')

edited Oct 17 '19 at 19:12

answered Oct 17 '19 at 18:59

Gordon Linoff

1,242,037
58
646
786

syntax to use near 'day and start_time < date('2019-09-15') – Sachin Shah Oct 17 '19 at 19:02

How to add first day and last day of month in between query in sql?

2 Answers2