CODEWITHSUNDEEP
mysqlsql
Rakid Naomi
Rakid Naomi

Reputation: 61

Create a dynamic time range in sql

Is there a way to create a dynamic way to make a sql query that queries the data between the first day and the last day of every current month? I have a field called created and and I want to get the data where created is between the first day and the last day of the month.

Upvotes: 0

Views: 502

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270473

Is there a way to create a dynamic way to make a sql query that queries the data between the first day and the last day of every current month?

A simple method uses just date functions:

where created_at >= curdate() + interval (1 - day(curdate()) day and
      created_at < (curdate() + interval (1 - day(curdate()) day) + interval 1 month

In addition to only using date arithmetic, this doesn't have an error of missing the last second of the month.

Upvotes: 0

matigo
matigo

Reputation: 1461

Something like this will do it:

WHERE created BETWEEN DATE_FORMAT(Now(), '%Y-%M-01 00:00:00') AND DATE_FORMAT(DATE_ADD(DATE_ADD(Now(),INTERVAL 1 MONTH), INTERVAL -12 HOUR), '%Y-%M-%d 23:59:59')

It’s not pretty, but it gets the job done.

Upvotes: 1

Related Questions