SQL: how to compare date values within nvarchar datatypes

Question

How can I build a query statement like this:

select * from men where Tdate between 01/01/01 and 02/02/02

The Tdate column is of type nvarchar

I am working with SQL Server Compact Edition (sqlCE)

Thanks

Answer 1

SELECT 
  * 
FROM
  men 
WHERE
  CONVERT(DATETIME, Tdate) BETWEEN '01/01/01' and '02/02/02'

It depends on the date format and on the current DATEFORMAT seting whether CONVERT() works or not.

It is advisable to convert the column to an actual DATETIME data type.

Answer 2

You're probably looking for a CAST or CONVERT statement in the WHERE clause on your nvarchar column.

SELECT * 
FROM Men 
WHERE CAST(Tdate AS Datetime) 
BETWEEN '01/01/09' and '02/02/09'

Here's a proof of concept:

declare @sample table
(
   TDate nvarchar(100)
)

Insert into @sample (TDate )  --yes, this INSERT work on SQL 2008 only :)
Values ('jan 1 2009'),('jan 10 2009'), ('feb 8 2009'),('feb 12 2009')

SELECT * 
FROM @sample 
WHERE CAST(Tdate AS Datetime) BETWEEN '01/01/09' AND '02/10/09'