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.
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...
I'm not around a development environment to post any code, but give
this link a try. It covers the SQL necessary to pull the previous month and year (the two parameters that you will need to determine a specific month).
Modifed Example from Link Provided
SELECT *
FROM YourTable
WHERE DATEPART(m, PlannedDate) = DATEPART(m, DATEADD(m, -1, GETDATE()))
AND DATEPART(y, PlannedDate) = DATEPART(y, DATEADD(m, -1, GETDATE()))
Select Month(DATEADD(m, -1, GETDATE())) --- Select Previous month only
Select year(GETDATE()),year(DATEADD(m, -1, GETDATE())) --- Select Previous year
Why we select the previous year?, suppose the current month is janaury and if you select the previous month alone mean it will return the value the 12 and select the records in current year itself...
CASE WHEN MONTH(a.Planned_Date)=Month(DATEADD(m, -1, GETDATE()))
AND YEAR(a.Planned_date) in (year(GETDATE()),year(DATEADD(m, -1, GETDATE()))) THEN a.Planned_Material_qty ELSE 0 END AS Prev_Month
Please learn how Dateadd and datediff function work. They are very powerful function when you work with datetime(date) data.
You can use these functions to find the begining of months.
SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1,0) as StartOfLastMonth
SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0) as StartOfThisMonth
You can use the following in your case:
CASE WHEN a.Planned_Date>=DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1,0) AND a.Planned_Date< DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0)
THEN a.Planned_Material_qty ELSE 0 END AS Prev_Month
naveensenaga...
Member
30 Points
85 Posts
Problem with Date function in SQL
Feb 27, 2013 03:13 AM|LINK
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.
</div> </div>
Regards
Naveen Sanagasetti...
Rion William...
All-Star
27374 Points
4542 Posts
Re: Problem with Date function in SQL
Feb 27, 2013 03:39 AM|LINK
I'm not around a development environment to post any code, but give this link a try. It covers the SQL necessary to pull the previous month and year (the two parameters that you will need to determine a specific month).
Modifed Example from Link Provided
raghavendra ...
Participant
1890 Points
435 Posts
Re: Problem with Date function in SQL
Feb 27, 2013 03:40 AM|LINK
Try this
Pbalan.in
Contributor
2142 Points
483 Posts
Re: Problem with Date function in SQL
Feb 27, 2013 04:03 AM|LINK
Try this...
Select Month(DATEADD(m, -1, GETDATE())) --- Select Previous month only
Select year(GETDATE()),year(DATEADD(m, -1, GETDATE())) --- Select Previous year
Why we select the previous year?, suppose the current month is janaury and if you select the previous month alone mean it will return the value the 12 and select the records in current year itself...
CASE WHEN MONTH(a.Planned_Date)=Month(DATEADD(m, -1, GETDATE()))
AND
YEAR(a.Planned_date) in (year(GETDATE()),year(DATEADD(m, -1, GETDATE())))
THEN a.Planned_Material_qty ELSE 0 END AS Prev_Month
Dont forget to mark as answer
limno
All-Star
117336 Points
8003 Posts
Moderator
MVP
Re: Problem with Date function in SQL
Feb 27, 2013 06:37 PM|LINK
Please learn how Dateadd and datediff function work. They are very powerful function when you work with datetime(date) data.
You can use these functions to find the begining of months.
SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1,0) as StartOfLastMonth
SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0) as StartOfThisMonth
You can use the following in your case:
CASE WHEN a.Planned_Date>=DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1,0) AND a.Planned_Date< DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0)
THEN a.Planned_Material_qty ELSE 0 END AS Prev_Month
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm