Question: How to get the data from sql using Date, I'm using the date datatype as varchar, I want to fetch the record from sql table. I want to fetch the last month data, for eg today date is 04/11/2014. From today minus one month 04/10/2014 so I want to search by this date 04/10/2014

I have tried this query.

```
SELECT * 
FROM [IndxRpt] 
WHERE EcrCylinderNo IS NULL 
  AND OrderDate <= '04/10/2014'

```

And also I tried this:

```
SELECT * 
FROM [IndxRpt] 
WHERE EcrCylinderNo IS NULL 
  AND OrderDate <= (CONVERT(VARCHAR(50), '04/10/2014', 103)) 

```

But its showing the November month data also. How to get this?

Answer: 
1) If your OrderDate column type is varchar than for string value "<=" is not a good operator but if its datatype is date, time or datetime than only "<=", ">=" will work fine. So better to convert it into datetime and than compare it.

2) If you have starting and ending date than its better to convert your OrderDate column to datetime and then compare it using "between" clause. 

Eg.

```
SELECT * 
FROM [IndxRpt] 
WHERE EcrCylinderNo IS NULL 
AND (CONVERT(DATE, OrderDate) BETWEEN CONVERT(DATE, GETDATE()) AND CONVERT(DATE, '04-OCT-2014')) 
--If SQL Server 2008 or above 

--Or For below
SELECT * 
FROM [IndxRpt] 
WHERE EcrCylinderNo IS NULL 
AND (CONVERT(DATETIME, OrderDate) BETWEEN GETDATE() AND CONVERT(DATETIME, '04-OCT-2014')) 

```
If for some reason you cannot change the column to a proper data type i would suggest converting it to datetime for your query.

E.G. 

```
SELECT * 
FROM [IndxRpt] 
WHERE EcrCylinderNo IS NULL 
  AND CAST(OrderDate as DateTime)<=  '04/10/2014'

```