retrieve data between two dates

Question

select count(DISTINCT pr.proip)as ipcount,pr.prousername,pr.prodate from processinfo pr
right join processinfo pf on pr.prousername=pf.prousername
where pf.prousername='pk'
and convert(nvarchar,pr.prodate)>= '28-10-2016' and convert(nvarchar,pf.prodate)<= '02-11-2016'

group by pr.prousername,pr.prodate

and result is below-

I want to ask why "02-11-2016" data is not shown while "02-11-2016" data is available in table.

please tell me how to fetch record between two dates

Nitin Sontakke · Answer

If the column in table is date (or datetime) please do not convert it to varchar (nvarchar). There is no need to do so. The query like pr.prodate between '2016-10-26' and '2016-11-02' should be fine. Notice the date format I have used. I call it universally neutral date format. It is impossible to misinterpret it. If you have stored date in a varchar (or nvarchar) column you will have hard time. Because the format you have used is not correct. If this is the case I suggest you redesign the table and have proper date (or datetime) column.

Nitin Sontakke · Answer

The datatype of your column is now date (and not datetime). Please confirm. It is very important that you confirm. If yes your query should be like this: where pf.prodate between '2016-10-01' AND '2016-11-30' That's it. No conversion is required on either side of the expression. Also first try without the where clause if rows are returned. Only when you see the rows are returned then only add the where clause. If no rows are returned without where clause there is problem with join etc. and not the where clause.

adam gills · Answer

Sir  I have change datatype as Date and formate as yyyy-MM-dd of column pr.prodate but when execute application it gives an error as Conversion failed when converting date and/or time from character string. Although I have try conversion property but not get result as per required. plz tell me where is the problem in conversion technique. select DISTINCT lg.usernamepf.prodatecount(DISTINCT pf.proip)as ipcount from login as lg right join processinfo pf on lg.username=pf.prousername where (pf.prodate) between convert(varchar(26)'01-10-2016'23) AND convert(varchar(26)'30-11-2016'23)  + group by lg.usernamepf.prodate;