Click here to Skip to main content
15,860,861 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi everybody,

I have a doubt in Date function in SQL.
Assuming this is the month of Jan 2013.
Now, i want to display the previous month quantity.
for that i wrote the Query is like this, but this is technically wrong in some cases this condition is failed.
How to get previous month (Dec 2012) details.


SQL
CASE WHEN MONTH(a.Planned_Date)=(MONTH(GETDATE())-1) AND ((YEAR(a.Planned_date)=YEAR(GETDATE())) OR (YEAR(a.Planned_date)=YEAR(GETDATE())-1))  
				THEN a.Planned_Material_qty ELSE 0 END AS Prev_Month



can anybody give me some suggestion out of this...
Posted

see the sql
SQL
select getdate()
will return current datetime
example: 2013-02-26 23:18:57.660


SQL
select DateAdd(mm,-1,getdate())
will return previous month of current datetime. example:2013-01-26 23:18:57.660

I hope you can solve your problem now.
 
Share this answer
 
Comments
Naveen.Sanagasetti 26-Feb-13 23:26pm    
FOR ex this is the month of jan 13, previous month means dec 12. using that dateadd function whatever you given using that it's satisfied the date condition , here my doubt is year also changed na..? that's the confusion now
for this i tried like this DATEPART(M,a.Planned_Date)=DATEPART(M,DATEADD(M,-1,GETDATE())) AND DATEPART(yy, a.Planned_Date) = DATEPART(yy, DATEADD(m, -1, getdate())) , this is ok or any issues
S. M. Ahasan Habib 26-Feb-13 23:37pm    
Yes. You can check with
declare @dt datetime = '01-Jan-2013';
select (DateAdd(mm,-1,@dt))
You can use the filter like
select day(DateAdd(mm,-1,getdate())), month(DateAdd(mm,-1,getdate())), year(DateAdd(mm,-1,getdate()))
Naveen.Sanagasetti 26-Feb-13 23:45pm    
K fine i checked that query it's working fine but here it's displaying yyyy-mm-dd also , but i want to display quantity based on prev month only , in this case no need to consider date
Hi Naveen

You can use the below function to get previous month...

DECLARE @Input AS DATETIME
SET @Input = NULL;
WITH CurrentDate AS
	(
	   SELECT ThisMonthFirstDay = DATEADD( MM, DATEDIFF(MM,0,ISNULL(@Input,GETDATE())) ,0)
	)
	, Numbers(N) AS
	(
				  SELECT -1 --PREVIOUS MONTH
		--SELECT 0		    --CURRENT MONTH
		--UNION ALL SELECT -1 
		--UNION ALL SELECT -2
	)
	SELECT  SlNo      = CrsApp.N + 3
	       ,[Month]     = DATENAME(M, CrsApp.StartDates) + ' ' + CAST(YEAR(CrsApp.StartDates) AS VARCHAR(4))
	       ,StartDate =	CONVERT(VARCHAR(10), CrsApp.StartDates, 120) 
		   ,EndDate   = CONVERT(VARCHAR(10), DATEADD(DD,-1, DATEADD(MM,1,CrsApp.StartDates)), 120)
	FROM   CurrentDate  CD
	CROSS APPLY (
	              SELECT N , DATEADD(MM,N.N,CD.ThisMonthFirstDay) 
				  FROM Numbers N
				 ) CrsApp(N, StartDates)
SQL
<pre lang="SQL">


Regards
Willington
 
Share this answer
 
Here is a great and easy way to accomplish this: Source[^]
SQL
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900