CODEWITHSUNDEEP
sql-server
Dhanil Dinesan
Dhanil Dinesan

Reputation: 575

How to get last six month of data from DB using SQL Server?

I have tried many different queries that already given here, but it also shows previous year data, for example, if use this query 

 Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)

or 

DATEADD(m, -6, current_timestamp)

These will result in the last six months. In my case, I want to print the last six month data. But the problem is if there is a data with the same date and different in the year will also result with these query eg (the above query returns the result of '2018-03-27 10:04:52.537' and if there is a data with '2017-03-27 10:04:52.537' will also come with the result). Can anyone help me?

here is my query 

DECLARE @date6MonthAgo DATETIME = DATEADD(m, -6, current_timestamp) 
DECLARE @totalCount INT = (SELECT COUNT(*) 
                           FROM   [PSA].[ProductionOrder] 
                           WHERE  CreatedDate >= @date6MonthAgo) 
DECLARE @openCount INT = (SELECT COUNT(*) 
                          FROM   [PSA].[ProductionOrder] 
                          WHERE  DocumentStatusCode=7 
                          AND    CreatedDate >= @date6MonthAgo ) 

SELECT @date6MonthAgo, @totalCount, @openCount

Upvotes: 0

Views: 337

Answers (2)

Aakash Singh
Aakash Singh

Reputation: 1062

You can use this for find the last 6 month data from current date,

DECLARE @fromdate as datetime, @todate as datetime

set @fromdate = (select DATEADD(month, -6, getdate()))
set @todate = getdate()

SELECT CreatedDate, keycolumnID
FROM   [PSA].[ProductionOrder] 
WHERE  DocumentStatusCode=7 
AND    cast(CreatedDate as date) between  cast(@fromdate as date) and  cast(@todate as date)
ORDER BY CreatedDate;

If you want find last 6 month date from a specific date, so you can set that date in @fromdate and @todate parameters like this,

set @fromdate = (select DATEADD(month, -6, @Yourdate))
set @todate = @Yourdate

Upvotes: 1

donPablo
donPablo

Reputation: 1959

Try something like this, after putting in the correct IDcolumn, just to verify for yourself the dates that are being returned

DECLARE @date6MonthAgo DATETIME = DATEADD(m, -6, current_timestamp); 
SELECT  @date6MonthAgo;

SELECT CreatedDate, keycolumnID
FROM   [PSA].[ProductionOrder] 
WHERE  DocumentStatusCode=7 
AND    CreatedDate >= @date6MonthAgo 
ORDER BY CreatedDate;

Upvotes: 2

Related Questions