select rows based on year and month

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

I have a table

SQL

register(Empname varchar(),punchadte varchar(40)).

I have created a SP and I pass year and month seperately.
I want to select rows based on year and month which are passed and read every rows.

Posted 4-Jun-12 20:21pm

Member 7909353

Updated 4-Jun-12 20:22pm

Prasad_Kulkarni

v2

Add a Solution

3 solutions

Solution 1

I think the DATEPART function can be used for this purpose as shown below:

SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetRowsByYearAndMonth 
	@Year int , 
	@Month int 
AS
BEGIN
	SELECT * 
	FROM register
	WHERE DATEPART(YEAR,punchdate)= @Year AND DATEPART(MONTH,punchdate)=@Month
END
GO

--Pass the year and month like
EXEC GetRowsByYearAndMonth 1998, 5

Posted 4-Jun-12 21:20pm

VJ Reddy

Comments

Member 7909353 5-Jun-12 6:37am

Here punchdate is not a singal value its collection of value i.e. column value.
Because when I run SELECT *
FROM register
WHERE DATEPART(YEAR,convert(datetime,(select PunchDate from InOutRegister)))= @Year AND DATEPART(MONTH,convert(datetime,(select PunchDate from InOutRegister)))=@Month
it gives an error....
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

VJ Reddy 5-Jun-12 6:58am

Use TOP 1 to take the first row in the subquery as follows
(select TOP 1 PunchDate from InOutRegister)

Member 7909353 5-Jun-12 7:21am

I want every row which satisfies condition year and month

Solution 3

SQL

SELECT *
    FROM register
    WHERE YEAR(punchdate)= @Year AND MONTH(punchdate)=@Month

Posted 6-Jun-12 3:03am

kishorekke

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)

Member 7909353 · Accepted Answer · 2012-06-05T22:39:00

SQL

ALTER PROCEDURE dbo.AttendenceReportSP

	(
	@Option int,
	@compname varchar(30),
	@deptname varchar(30),
	@year varchar(4),
	@month varchar(10)
	)
	
AS
begin



    declare @datetimestring varchar(30),@loccode varchar(30),@DateTime datetime,@years varchar(4),@months varchar(10),@ff varchar(12),@y int,@m int,@empcode int --declaration section
    
    if @option=0
       begin
       select @y=convert(int,@year)
       if @month=convert(varchar,'January')
       select @m=convert(int,'01')
       else if @month=convert(varchar,'February')
       select @m=convert(int,'02')
          else if @month=convert(varchar,'March')
       select @m=convert(int,'03')
          else if @month=convert(varchar,'April')
       select @m=convert(int,'04')
          else if @month=convert(varchar,'May')
       select @m=convert(int,'05')
          else if @month=convert(varchar,'June')
       select @m=convert(int,'06')
          else if @month=convert(varchar,'July')
       select @m=convert(int,'07')
          else if @month=convert(varchar,'August')
       select @m=convert(int,'08')
          else if @month=convert(varchar,'September')
       select @m=convert(int,'09')
          else if @month=convert(varchar,'October')
       select @m=convert(int,'10')
          else if @month=convert(varchar,'November')
       select @m=convert(int,'11')
          else if @month=convert(varchar,'December')
       select @m=convert(int,'12')
       
       
     select @ff=PunchDate from register
        select @DateTime=convert(datetime,@ff,101)
        select @years=DATEPART(year,convert(datetime,@DateTime))
        select @months=DATEPART(month,convert(datetime,@DateTime))
       select * from registerwhere @y=@years and @m=@months
       end
RETURN
end