Running sql from Query Analyzer and VB and getting different results

[gillianm1]

Hi,
Can someone please help. I am running the following sql from the query analyzer and vb:

select * from DWCSYPER where convert(datetime,CPFDAT) < '02/01/2000'

Where CPFDAT is a field with datatype char.
When run from vb, I get no records - when run from the Analyzer, I get one record (correct). VB doesn't seem to like the where clause. Does anyone know why?

BTW I can't change the design of the table to make CPFDAT into a date datatype as values are imported into the table in text format.

Thanks,
Gillian
[sig][/sig]

 

[swilliams]

It could have something to do with local settings - whenever I compare dates in where clauses I always format them to mmm dd, yyyy as then there is no ambiguity about being dd/mm or mm/dd format.
Also I put #'s around dates - I would have thought that your would create sql statement would return an error as there aren't any #'s around the date.

Simon [sig][/sig]

 

[steveblum]

SQL Server will treat the expression '02/01/2000' as a string not a date, but it won't give an error, it will just allow a date and a string to be compared. The result doesn't usually give the desired effect though, unless you order the date as yyyy/mm/dd. At least then the less-than comparison would always work.

It is better to make them both dates, as swilliams suggested, but using the expression #02/01/2000#. [sig][/sig]