Where Clause to Find All Records in a Specific Month

WHERE Clause to find all records in a specific month

I think the function you're looking for is MONTH(date). You'll probably want to use 'YEAR' too.

Let's assume you have a table named things that looks something like this:

id happend_at
-- ----------------
1  2009-01-01 12:08
2  2009-02-01 12:00
3  2009-01-12 09:40
4  2009-01-29 17:55

And let's say you want to execute to find all the records that have a happened_at during the month 2009/01 (January 2009). The SQL query would be:

SELECT id FROM things 
   WHERE MONTH(happened_at) = 1 AND YEAR(happened_at) = 2009

Which would return:

id
---
1
3
4

Get all rows from a specific month and year

You can add that condition in the WHERE clause of your select statement. I would recommend using BETWEEN operand for two dates:

SELECT myColumns
FROM myTable
WHERE dateColumn BETWEEN '2013-02-01' AND '2013-02-28';

If you mean to say you want everything beginning with February 2013, you can do so using the greater than or equal to operator:

SELECT myColumns
FROM myTable
WHERE dateColumn >= '2013-02-01';

EDIT

While the above are my preferred methods, I would like to add for completeness that MySQL also offers functions for grabbing specific parts of a date. If you wanted to create a paramaterized query where you could pass in the month and year as integers (instead of a start and end date) you could adjust your query like this:

SELECT myColumns
FROM myTable
WHERE MONTH(dateColumn) = 2 AND YEAR(dateColumn) = 2013;

Here is a whole bunch of helpful date and time functions.

SQL select records with current month

My goal to select the records with current month (2020-04-XX)

Here is one option:

where NewMonth >= datefromparts(year(getdate()), month(getdate()), 1)

If you need an upper bound too:

where 
    NewMonth >= datefromparts(year(getdate()), month(getdate()), 1)
    and NewMonth < dateadd(month, 1, datefromparts(year(getdate()), month(getdate()), 1))

If you want the previous month:

where 
    NewMonth >= dateadd(month, -1, datefromparts(year(getdate()), month(getdate()), 1))
    and NewMonth < datefromparts(year(getdate()), month(getdate()), 1)

Or two months ago:

where 
    NewMonth >= dateadd(month, -2, datefromparts(year(getdate()), month(getdate()), 1))
    and NewMonth < dateadd(month, -1, datefromparts(year(getdate()), month(getdate()), 1))

Is there a way to search just month and day in SQL?

The SQL standard syntax would be:

where extract(day from date) = 25 and
      extract(month from date) = 12

Most databases, however, support the day() and month() functions.

Get all records by month in SQL

Here is a query for the current month:

SELECT *
FROM [dbo].[vPolicy_Renewal_90DayLookUp] AS PRD
WHERE
    PRD.ExpDate >= DATEADD(mm, DATEDIFF(mm, 0, @mydate), 0) AND
    PRD.ExpDate < DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm, 0, @mydate), 0));

For the second month, use this WHERE clause:

WHERE
    PRD.ExpDate >= DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm, 0, @mydate), 0)) AND
    PRD.ExpDate < DATEADD(mm, 2, DATEADD(mm, DATEDIFF(mm, 0, @mydate), 0));

And continue this way for the third and subsequent months. If you want just a single query for the current and next two months, then use this:

SELECT *
FROM [dbo].[vPolicy_Renewal_90DayLookUp] AS PRD
WHERE
    PRD.ExpDate >= DATEADD(mm, DATEDIFF(mm, 0, @mydate), 0) AND
    PRD.ExpDate < DATEADD(mm, 3, DATEADD(mm, DATEDIFF(mm, 0, @mydate), 0))
ORDER BY
    PRD.ExpDate;

You should see each of the three months, in separate blocks, from earliest record to latest record.

SQL Query to select data based on year and month

To find records of some month, update your where clause to :

where CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'

How to get all records before any specified month in mysql

This will do (assuming there are no future dates):

SELECT SUM(amount) as allPreviousAmount 
FROM `fn_table`
WHERE MONTH(transdate) < ? OR YEAR(transdate) < YEAR(CURRENT_DATE())

Replace ? with the month that you want the results for.

---

Related Topics