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

7 Years
Discussion Span
Last Post by Akash Saikia

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.

Edited by buddylee17: n/a

Votes + Comments
So simple solution.

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

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

Enjoy... :)

Edited by Akash Saikia: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.