SQL Server - select date between from date to to date

Asked By kshama parashar on 12-Sep-11 05:13 AM
hello to all

i have a table in which i have a column of date of joining and the data type of this column is nvarchar(max)
now i want to search date between from date to to date but m not able to get date by the query and i don't want to change data type nvarchar to datetime.

my query is
select * from tablename where DOJ between '15/06/2011' and '10/08/2011'

date format is dd/MM/yyyy.
Reena Jain replied to kshama parashar on 12-Sep-11 05:15 AM
hi,

to get the record between two dates, here is the query

Select * from table1 where Convert(datetime, mydate, 103)  between
Convert(datetime, @Fromdt, 103) and Convert(datetime, @Todt, 103)

Hope this will help you
Jitendra Faye replied to kshama parashar on 12-Sep-11 05:20 AM

Before comparing date you must convert both date in same format.

use this query-

select * from table1 where WHERE datecol between Convert(varchar(20), fromdatecolumn, 101) and CONVERT(datetime, todatecolumn, 101)

Try this and let me know.

kshama parashar replied to Reena Jain on 12-Sep-11 05:26 AM
thanks reena i got all the dates but with some error message like

Arithmetic overflow error converting expression to data type datetime.


aneesa replied to kshama parashar on 13-Sep-11 12:13 AM
select * from tablename where convert(datetime,doj,103) between convert(datetime,'15/06/2011',103) and convert(datetime,'13/12/2011',103)