Get Records between two dates

[MARKAND911]

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

[buddylee17]

Use BETWEEN and cast the varchar to date:

select * from mytable where dtcreated BETWEEN CAST(fromdate AS DATE) and CAST(todate AS DATE)

Edit: This works on SQL Server 2008 and up. Cast to datetime if you are using an earlier version.

[debasisdas]

You simply need to understand why your query failed.

To compare, you need dates not strings.

[Akash Saikia]

In the below given example,you just need to specify
1> Your Table name from any specific MSSQL database
2> 'ColumnName' as a table field,that should be in datetime or date format in the table
Example

<strong>select * from [yourTableName]
where  (CAST(ColumnName AS date) >= '2006-01-18') and (CAST(ColumnName AS date) <= '2006-02-18')</strong>

Enjoy... :)