Running sql from Query Analzer 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]

 

[Nanda]

VB does like Where command but does not understand Convert. try converting your data in your required format in VB then, pass with Where clause.

HTH

 

[gillianm1]

Surely though, if I have the Convert in quotes as part of the sql statement - then VB won't try to interpret it? It should send everything inside the quotes to SQL Server. [sig][/sig]

 

[Nanda]

Well! You maybe using jet or ODBC to send the command. Problem is kind of same as if u write date in # for VB and in quotes (') for sql.

I may be wrong. It is just a thought, if helps.

 

[Nanda]

another guess...
try running your statement in query analyser in parts and see if Convert(datetime, fld) returns 02/01/2000 not 01/02/2000.

 

[gillianm1]

Thanks for the suggestions. Whether the Convert() returns 02/01/2000 or 01/02/2000 shouldn't matter though as there are records in the database for both. I know what you're getting at but in this case the problem seems to be something else. I'm sending the query through ODBC btw.

I think what I'm probably going to do is to leave the dates as text fields (that's how they're imported to my table anyhow) and compare them as such. I should be able to do that because the format is yyyymmdd. Once I have the comparisons done I'll convert them to date format.
Nothing is ever simple!
Thanks for your help. [sig][/sig]

 

[calahans]

Have you ever used the query Profiler (on v 7) or SQl Trace (on 6 and 6.5) utilities. You can trace the actual SQL sent by your application (after ODBC has had its wicked way). Often what you code and what gets eventually sent is different. Identify the SQL sent and copy to analyser and make sure it works... [sig]<p>Cal<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[foxdev]

Good point, Cal; your post reminded me of several times when doing an ODBC trace turned up some unexpected tampering with the statements.

Gillian, could you use DTS to import the text file? DTS can do some data transformations (!) when importing, so you should be able to read in the text but store it as a SmallDateTime. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[gillianm1]

I've never used the Profiler before so thanks for the tip Cal.
The DTS option sounds promising too. Right now though I'm going to leave the dates as text fields. It seems to be working fine. I'll probably go back and look at it some more when I've more time.
Thanks for all your help,
Gillian [sig][/sig]