How to get values between two dates?

Question

I create a database table.

EmployeeTable: that is employee_tb

emp_no	nvarchar(50)
emp_name	nvarchar(50)
dept	nvarchar(50)
emp_doj	nvarchar(50)
emp_dor	nvarchar(50)

After Inserted Values:

emp_no	emp_name	dept	emp_doj	emp_dor
E-1001	priya prakash	CHEM	12-Feb-1980	10-Mar-2018
E-1002	riya prakash	MATH	24-Jan-1990	25-Feb-2018
E-1003	jiya prakash	ENG	05-Dec-1995	13-Feb-2018
E-1004	diya prakash	PHYS	19-May-1995	01-Apr-2018
E-1005	piya prakash	COM	10-Jun-1999	24-Jan-2018

My Output Will Happen:

I want to display result that is those employee who has retirement date from 01-Jan-2018 to 28-Feb-2018 with asc order

emp_no	emp_name	dept	emp_doj	emp_dor
E-1005	piya prakash	COM	10-Jun-1999	24-Jan-2018
E-1003	jiya prakash	ENG	05-Dec-1995	13-Feb-2018
E-1002	riya prakash	MATH	24-Jan-1990	25-Feb-2018

I use this sql:

SELECT * FROM employee_tb WHERE cast(emp_dor as date) between '01-Jan-2018' AND '28-Feb-2018' order by emp_dor asc

but error.

Please help me.

Nitin Sontakke · Answer

There is ABSOLUTELY no error. Works PERFECTLY okay. declare @employee_tb table ( emp_no nvarchar(50) ,emp_name nvarchar(50) ,dept nvarchar(50) ,emp_doj nvarchar(50) ,emp_dor nvarchar(50) ) insert into @employee_tb values ( 'E-1001' , 'priya prakash' , 'CHEM' , '12-Feb-1980' , '10-Mar-2018' ) ,( 'E-1002' , 'riya prakash ' , 'MATH' , '24-Jan-1990' , '25-Feb-2018' ) ,( 'E-1003' , 'jiya prakash ' , 'ENG ' , '05-Dec-1995' , '13-Feb-2018' ) ,( 'E-1004' , 'diya prakash ' , 'PHYS' , '19-May-1995' , '01-Apr-2018' ) ,( 'E-1005' , 'piya prakash ' , 'COM ' , '10-Jun-1999' , '24-Jan-2018' ) SELECT * FROM @employee_tb WHERE cast (emp_dor as date ) between '01-Jan-2018' AND '28-Feb-2018' order by cast (emp_dor as date ) asc The ORDER BY will not work as expected and you should have mentioned that.

Nitin Sontakke · Answer

Wouldn't it be better to share error, please?

Shahriar Nikkhah · Answer

Nitin has the right answer, and please don't use REPLACE clause, if you are going to make any changes fix the fields data types for the DATES Good luck Nik

Laxmidhar Sahoo · Answer

Use convert function for varchar to date time But first replace - to space Like Replace('03-feb-2018','-',' ') Again convert like Convert(date time,replace('03-feb-2018','-',' '),106)

Manav Pandya · Answer

Hello You can follow one article that describes same situation as you have faced : https://www.codeproject.com/Tips/355235/How-to-search-between-two-dates-and-get-all-record Thanks