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

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