how to cal a datetime colum with int values

Prasad Bhagat

11y1.1k

Dear all,

i have a procedure like bellow,

CREATE PROC USP_ABC

@month int,

@year int ,

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pankaj Kumar Choudhary11y ago
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

0
Saroj Kumar Sahu11y ago
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
0
Prasad Bhagat11y ago
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
0
Saroj Kumar Sahu11y ago
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.
0