How to search data from month and year

Question

0.00/5 (No votes)

See more:

SQL

Declare @year Varchar(100)=2018
Declare @month Varchar(100)=3

SELECT Shrimp_Name,Grades,Japan,EU,US  
 FROM (  
 SELECT MSN.Shrimp_Name,TMG.ID,TMG.Grades,TMR.Region_Name,TSP.Price from   
 Tbl_Shrimp_Price TSP  
 inner join Tbl_Mast_Shrimp_Region TMR On  TMR.ID=TSP.Region  
 inner join Tbl_Mast_Shrimp_Grades TMG On TMG.ID=TSP.Grade  
 inner join Tbl_Mast_Shrimp_Name MSN On MSN.ID=TSP.ShrimpType  
 WHERE TSP.ShrimpType=1 and TSP.PeriodType=@PeriodType AND 
 --PostMonth =cast('01-'+@year1 as Varchar(20))
 year(PostMonth) = @year AND  
 MONTH(PostMonth)=@month  
 ) as s  
 PIVOT  
 (  
 SUM(Price)  
 FOR [Region_Name] IN (Japan, EU, US)  
 )AS pvt  
 ORDER BY Shrimp_Name,Grades

SQL

SELECT Shrimp_Name,Grades,Japan,EU,US
FROM (
SELECT MSN.Shrimp_Name,TMG.ID,TMG.Grades,TMR.Region_Name,TSP.Price from
Tbl_Shrimp_Price TSP
inner join Tbl_Mast_Shrimp_Region TMR On  TMR.ID=TSP.Region
inner join Tbl_Mast_Shrimp_Grades TMG On TMG.ID=TSP.Grade
inner join Tbl_Mast_Shrimp_Name MSN On MSN.ID=TSP.ShrimpType
WHERE TSP.ShrimpType=2  and TSP.PeriodType=@PeriodType
AND --PostMonth = cast('01-'+@year1 as Varchar(20))
year(PostMonth) = @year AND
MONTH(PostMonth)=@month
) as s
PIVOT
(
SUM(Price)
FOR [Region_Name] IN (Japan, EU, US)
)AS pvt
ORDER BY Shrimp_Name,Grades

What I have tried:

above is my Query i want to search Data from month and Year but when i search Data From this Query at the Location of @month i put Day means(@month=1) then this Query Select Data so how to Search Data From Month and Year Please Help Me-

PostMonth is my Column Name ans Save Data Format Below-
01-03-2018

Posted 2-Apr-18 3:22am

Member 12183079

Updated 2-Apr-18 4:05am

Wendelius

v3

Add a Solution

Comments

MadMyche 2-Apr-18 9:44am

When posting DB related questions, it is recommended to add in what type of RDBMS is being used as well as the table schema.
When dealing with dates and/or time, it is often easiest to work with the DATE datatype and then using INT for the portions (year, month, day). Using a text (VARCHAR) type and trying to do math within that often ends up doing something you didnt intend

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2018-04-02T04:06:00

If I understand correctly the date is stored in a a varchar column and taken that this is SQL Server. If that is the case, one easy way is to convert it to date and then do the comparison. For example

SQL

...
AND DATEPART(year, CONVERT(date, PostMonth, 105)) = @year 
AND DATEPART(month, CONVERT(date, PostMonth, 105)) = @month
...

However, the actual correction would be to store the date inside a date column instead. This would make it a lot easier to do the comparison and wuold leave out all unnecessary conversions. The same applies to the variables since it seems that you have used varchar for them even though they are actually numbers.