0

I am trying to write a query that returns only the rows which has Datetime to be between the day 01 of the month and current day. But I am not sure if it is possible to do. So, I am aware of the function NOW() that gives me the current DATETIME but I still don't know how to get the other end of BETWEEN automatically since its a web application and i don't want to make the user input the datetime.

Ex: I want all the orders that were made during the month where you are consulting. 

Select * from orders where date between (the function I am looking for) and NOW();
Tirthraj Barot
  • 2,671
  • 2
  • 17
  • 33
Gabriel Borges
  • 21
  • 3

2 Answers2

0
select * from table_name 
where (date between  DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), interval 30 day), interval 1 day) AND CURDATE() )

or Your kinda approach..

select * from table_name 
where (date between  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )

or you can also do this

select * from table_name 
where `date` between curdate() - dayofmonth(curdate()) + 1
                 and curdate()
Tirthraj Barot
  • 2,671
  • 2
  • 17
  • 33
0

This uses index better (if any)

DECLARE @iniDate DATE
DECLARE @endDate DATE

SET @endDate = curdate()
SET @iniDate = @endDate - dayofmonth(@endDate) + 1

SELECT *
FROM My_table
WHERE `My_date` BETWEEN @iniDate AND @endDate
Horaciux
  • 6,322
  • 2
  • 22
  • 41