CODEWITHSUNDEEP
sqlsql-server
jinaljagani
jinaljagani

Reputation: 37

How to fetch data of last month from database

I have a table where records for a user are stored

This includes 2 columns applied_date.,js_id.

now i have to count js_id have applied to number of job this month.

Upvotes: 2

Views: 386

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

For a specific month (given a date),

DECLARE @date SMALLDATETIME = '20120105'; -- for January, also could use CURRENT_TIMESTAMP
-- the above could also be a stored procedure parameter

SET @date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0);

SELECT COUNT(js_id) 
  FROM dbo.[table_name]
  WHERE applied_date >= @date
  AND applied_date < DATEADD(MONTH, 1, @date);

Upvotes: 2

Timur Sadykov
Timur Sadykov

Reputation: 11377

Select count(js_id) from yourtable   WHERE DATEDIFF( m, applied_date, GETDATE() ) = 0

Upvotes: 0

Michiel van Vaardegem
Michiel van Vaardegem

Reputation: 2035

Something like:

SELECT COUNT(js_id), MONTH(applied_date)
FROM table
GROUP BY MONTH(applied_date)

Upvotes: 2

Related Questions