How can I search or compare SQL correctly with DateTime values?

[Guest]

Hi
After I got DateTime from Calendar (ie: fDate = July 04,2004), the date is showed by Debug program is 07/04/2004 but after I using: strSQL = "SELECT * FROM Invoice WHERE InvoiceDT >= " & fDate ---> some how it converted fDate back as 04/07/2004 (April 07, 2004) and when execute the SQL I got wrong result

*) If I get the date later than 12 then there are no problem because MS detects it (ie: 30/06/2004 or 06/30/2004

1. How can I correct this problem?
2. Do I need special syntax in string SQL for DateTime? (I did try add "#" & fDate "#", the result is the same!

Many thanks for any help

Anh

 

[Miha Markic [MVP C#]]

Hi Anh,

You should realy use parametrized queries - the best way to deal with this
and other issues.
See
Parameters in Data-Adapter Commands
..net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi,
After I got DateTime from Calendar (ie: fDate = July 04,2004), the date is

showed by Debug program is 07/04/2004 but after I using: strSQL =
"SELECT * FROM Invoice WHERE InvoiceDT >= " & fDate ---> some how it
converted fDate back as 04/07/2004 (April 07, 2004) and when execute the SQL
I got wrong result!

*) If I get the date later than 12 then there are no problem because MS

detects it (ie: 30/06/2004 or 06/30/2004)

1. How can I correct this problem?
2. Do I need special syntax in string SQL for DateTime? (I did try add "#"

& fDate "#", the result is the same!)

 

[Kjartan Storli]

I have had the same problem. I solved it by creating a DateFormatInfo where
I specified
a format which the db would accept. Calling the tostring function with this
object will
generate a string with the correct format.

Hi,
After I got DateTime from Calendar (ie: fDate = July 04,2004), the date is

showed by Debug program is 07/04/2004 but after I using: strSQL =
"SELECT * FROM Invoice WHERE InvoiceDT >= " & fDate ---> some how it
converted fDate back as 04/07/2004 (April 07, 2004) and when execute the SQL
I got wrong result!

*) If I get the date later than 12 then there are no problem because MS

detects it (ie: 30/06/2004 or 06/30/2004)

1. How can I correct this problem?
2. Do I need special syntax in string SQL for DateTime? (I did try add "#"

& fDate "#", the result is the same!)