how to cal a datetime colum with int values

Question

Dear all i have a procedure like bellow CREATE PROC USP_ABC @month int @year int  as begin select * from tbl where (@month=0 or (SELECT DATEPART(MM DOJ) FROM dbo.tbl_param_employee_master as month)= @month)and (@year=0 or (SELECT DATEPART(yy DOJ) FROM dbo.tbl_param_employee_master as year)= @year) END DOJ Date. here DOJ datatype is date so please suggest me how to call this query

Pankaj Kumar Choudhary · Answer

Hello @Prasad i think their is a little mistake in your code you can use this stored procedure instead of your stored procedure CREATE PROC USP_ABC @month int @year int  as begin select * from tbl where DOJ IN (SELECT DOJ FROM dbo.tbl_param_employee_master where DATEPART(MM DOJ)=@month AND DATEPART(yyyy DOJ)=@year) END

Saroj Kumar Sahu · Answer

Hi Prasad  Can u please try with the below Create Proc USP_ABC @month INT @year INT AS BEGIN SELECT * FROM tbl WHERE DOJ IN(SELECT DOJ FROM dbo.tbl_param_employee_master WHERE DATEPART(MM DOJ)=@month AND DATEPART(yyyy DOJ)=@year) END

Prasad Bhagat · Answer

Dear saroj Thanks for ur quick reply . how can i retreve month or year based on above int value from date colum so pleae help me

Saroj Kumar Sahu · Answer

Hi Prasad In your Where condition u have written a sub query [ SELECT DATEPART(MM DOJ) FROM dbo.tbl_param_employee_master as month] which may return multiply records i.e more than 1 values which is not permitted. So please modify your subquery in where condition.