How to Get Records Created at The Current Month

Get records of current month

This query should work for you:

SELECT *
FROM table
WHERE MONTH(columnName) = MONTH(CURRENT_DATE())
AND YEAR(columnName) = YEAR(CURRENT_DATE())

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

How to get records created at the current month?

You need to enclose the where in a lamda as well.

scope :from_this_month, lambda { where("votes.created_at > ? AND votes.created_at < ?", Time.now.beginning_of_month, Time.now.end_of_month) }

Otherwise it may appear to work and your tests will all pass, but if your app runs for more than a month you will start to get incorrect results because Time.now is evaluated when the class loads, not when the method is called.

Fetch records of current month using PostgreSQL query

You can compare the month and year of a date with the current one. But the index by field will not be used, you can build a separate index by year and month for this.

select *
from your_table
where extract(YEAR FROM createdAt) = extract(YEAR FROM now())
      and extract(MONTH FROM createdAt) = extract(MONTH FROM now())

MYSQL query to show current month records count

You're close, COUNT() doesn't only count TRUE values, it counts any and all non-NULL values, so it's counting the FALSE values too.

Try...

SELECT
  COUNT(*),
  SUM(DATE_FORMAT(date, '%Y-%m-01') = DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'))
FROM
  orders

When summed, TRUE is treated as 1, and FALSE is treated as 0

I also use DATE_FORMAT() instead of MONTH() so you don't accidentally count the values from the same month in previous years.

• If you want that, just change the DATE_FORMAT() back to MONTH() and keep the SUM()

getting records for current month only

Change your CTE to this:

WITH times
        AS (
            SELECT t1.EmplID
                ,t3.EmplName
                ,min(t1.RecTime) AS InTime
                ,max(t2.RecTime) AS [TimeOut]
                ,cast(min(t1.RecTime) AS DATETIME) AS InTimeSub
                ,cast(max(t2.RecTime) AS DATETIME) AS TimeOutSub
                ,t1.RecDate AS [DateVisited]
            FROM AtdRecord t1
            INNER JOIN AtdRecord t2 ON t1.EmplID = t2.EmplID
                AND t1.RecDate = t2.RecDate
                AND t1.RecTime < t2.RecTime
            INNER JOIN HrEmployee t3 ON t3.EmplID = t1.EmplID
            WHERE MONTH(t1.RecDate)=MONTH(Getdate())
              AND YEAR (t1.RecDate)=YEAR(Getdate())
            GROUP BY t1.EmplID
                ,t3.EmplName
                ,t1.RecDate
            )

Please note that if you run this on the first of any month, you will get data only for that day and not for the previous month.

How to select just current month data in SQL?

Since you have not specified your DB Product but from your syntax It seems SQL Server. YOu can use MONTH function here -

Select *
FROM CORP.T_LOGI_RN_Planeacion_Entregas
Where MONTH(CORP.T_LOGI_RN_Planeacion_Entregas.Date) = MONTH(GETDATE())
  AND YEAR(CORP.T_LOGI_RN_Planeacion_Entregas.Date) = YEAR(GETDATE());

Retrieve MySql Records Within a Specific Month but Less than the current year and Less than the current Day

Use the functions DAY(), MONTH() and YEAR() in your conditions:

SELECT * 
FROM my_table 
WHERE MONTH(date) = MONTH(CURRENT_DATE)
  AND YEAR(date) < YEAR(CURRENT_DATE) 
  AND DAY(date) < DAY(CURRENT_DATE);

See the demo.

---

Related Topics