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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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

Anh Truong said:
Hi,
After I got DateTime from Calendar (ie: fDate = July 04,2004), the date is
Click to expand...
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
Click to expand...
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 "#"
Click to expand...
& fDate "#", the result is the same!)
 
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.

Anh Truong said:
Hi,
After I got DateTime from Calendar (ie: fDate = July 04,2004), the date is
Click to expand...
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
Click to expand...
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 "#"
Click to expand...
& fDate "#", the result is the same!)
 
Back
Top