Select dates between two dates using isnull function

Prasad Bhagat

11y1.1k

Dear All,

i have procedure like bellow

create proc usp_abc

@fromdate datetime,

@todate datetime

begin

select * from tbl

whete

(CONVERT(datetime,(select exit_status_Date from dbo.tbl_param_employee_exit_track where employee_d=tbl_param_employee_master.ID),103) between @fromdate and @todate)

i want to fetch all records between two dates if dates are null also

4 Replies

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

Jaipal Reddy11y ago
Accepted answer
Hi Prasad try the following code -

CREATE PROC USP_ABC
@FROMDATE DATETIME,
@TODATE DATETIME
AS
BEGIN
SELECT * FROM TBL
WHERE
CONVERT(DATETIME, (SELECT EXIT_STATUS_DATE FROM DBO.TBL_PARAM_EMPLOYEE_EXIT_TRACK WHERE EMPLOYEE_D=TBL_PARAM_EMPLOYEE_MASTER.ID) ,103)
BETWEEN (@FROMDATE OR @FROMDATE IS NULL) AND (@TODATE OR @TODATE IS NULL)
END
0
Prasad Bhagat11y ago
Hi jaipal , thanks for ur quick reply , but the query was showing some error if possible please solve it An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.dll but was not handled in user code Additional information: An error occurred while reading from the store provider's data reader. See the inner exception for details.
0
Jaipal Reddy11y ago
More over we could not restrict the date parameters if it is null.
0
Jaipal Reddy11y ago
AND ALSO TRY THIS WAY -

CREATE PROC USP_ABC
@FROMDATE DATETIME = NULL ,
@TODATE DATETIME = NULL
AS
BEGIN
SELECT * FROM TBL
WHERE
CONVERT(DATETIME, (SELECT EXIT_STATUS_DATE FROM DBO.TBL_PARAM_EMPLOYEE_EXIT_TRACK WHERE EMPLOYEE_D=TBL_PARAM_EMPLOYEE_MASTER.ID) ,103)
BETWEEN ISNULL(@FROMDATE, '1900-01-01') AND (@TODATE,'9999-12-31')
END

And also read this article once may you find the path. http://www.c-sharpcorner.com/Blogs/49237/
0