Get Records between two dates in MS SQL

Question

I have a table in which i have a column named "dtCreated" which has got datatype as VARCHAR(50)

Now I want records between two dates.

I wrote my query as written below

SELECT * FROM mytable WHERE dtcreated>=fromdate AND dtcreated<=todate

This query work fine when fromdate and ToDate are of same month. i.e if fromDate =1/1/2011
and todate is 1/31/2011 it will work fine

But if fromdate is 1/1/2011 and todate is 2/1/2011 then no records will be returned.

Please give me solution of it

Raju Katare · Answer

Hi Markand use following query you will get desired result SELECT * FROM mytable WHERE convert(varchar dtcreated 112) >= convert(varchar fromdate 112) AND convert(varchar dtcreated 112) convert(varchar todate 112)

Raju Katare · Answer

@ MARKAND BHATT Let me know that have you got the output?

Suthish Nair · Answer

resolved?

Suthish Nair · Answer

SELECT * FROM mytable WHERE dtcreated between fromdate AND todate