CODEWITHSUNDEEP
sql-server
user3343010
user3343010

Reputation: 85

SQL Selecting only records matching year and month between two dates

I am making a SQL procedure to get me a selection of workers from the table, where each worker has startJob and endJob date.

What I need is to make selection of all the workers who were on the job during specified @year and @month. (@year & @month are input params) I would really appreciate an advice how to solve this the easiest way.

Thx for any help.

Upvotes: 0

Views: 77

Answers (4)

Arun Prasad E S
Arun Prasad E S

Reputation: 10115

FINAL SOLVED SELECT ROWS BETWEEN TWO DATE USING ONLY MONTH AND YEAR

begin
declare @trvl_start_date as date
declare @trvl_stop_date as date

declare @trim_start as varchar(20)
declare @trim_end as varchar(20)

declare  @trvl_start_date_new as date
declare  @trvl_stop_date_new as date

set @trvl_start_date ='2015-10-12'
set @trvl_stop_date='2016-01-01'
set @trim_start = LEFT(@trvl_start_date, CHARINDEX('-', @trvl_start_date) + 2) + '-01'
set @trim_end = LEFT(@trvl_stop_date, CHARINDEX('-', @trvl_stop_date) + 2) + '-01'

print @trim_start
print @trim_end

set @trvl_start_date_new = @trim_start
set @trvl_stop_date_new =dateadd(month,-1,@trim_end)

print @trvl_start_date_new
print @trvl_stop_date_new

SELECT   fee_catg_srno      FROM   dbo.fee_catg_tbl
                                        WHERE   (acd_yr = 1) AND (recycle = 1) AND (acc_head_srno = 3) AND pay_date between @trvl_start_date_new and    @trvl_stop_date_new 


end

Upvotes: 0

Alex
Alex

Reputation: 5636

Create a DATE based on the start of the month for the given year

DECLARE @Year INT = 1999
DECLARE @Month INT = 1
DECLARE @Date DATE = CONVERT(DATE, CONVERT(CHAR, @Month) + '-' + CONVERT(CHAR, @Month) + '-01')

-- SQL Server should now correctly handle the logic to filter out the relevant rows
SELECT * 
FROM worker w
WHERE @Date BETWEEN w.startJob AND w.endJob

If you'd rather use the last day of the month, then you could use EOMONTH(@Month) for your DAY.

Upvotes: 0

SQLSam
SQLSam

Reputation: 537

You can make a date from the year and month parameters and compare to the start and end job fields. This will return the first day of the month/year entered, and return results where this date is between the StartJob and EndJob

select *
from worker

where
 CAST(
      CAST(@Year AS VARCHAR(4)) +
      RIGHT('0' + CAST(@month AS VARCHAR(2)), 2) +
      RIGHT('0' + CAST(01 AS VARCHAR(2)), 2) 
   AS DATETIME)

between 
StartJob and EndJob

Upvotes: 0

Roman Marusyk
Roman Marusyk

Reputation: 24569

If i proper understood you then try something like:

CREATE PROCEDURE [usp_Procedure]
  @Year  int
, @Month int
AS
BEGIN
  select * from workers
  where @Year between YEAR(startJob) and YEAR(endJob)
    and @Month between MONTH(startJob) and MONTH(endJob)
END

Upvotes: 1

Related Questions