SQL query and system DATE problem

[seegoon]

Hi to all.
I have a small problem I hope someone can help me with.
I am running a sql query to a csv file. The query searches for the
total of a column between 2 dates.

This is a copy of one of the query's:

Dim SF_SMT As New OleDb.OleDbCommand("Select count(*) from
fault_records.csv where F4 = '" & lblprod.Text & " ' " & " AND F8 AND
F1 between #" & SelectproductForm.datestart & "# and #" & Now.Date &
"#", conn)



F1 contains the date.F4 is the "product" and F8 the fault code that I'm
totaling.
The query works fine(returns the correct value) if my computer regional
DATE setting is m/d/yyyy. If I change it to d/m/yyyy then the query
returns nothing. It runs , but does not return anything.
Why is this and how can I get around it.

If this software is only running on one machine it would not be to much
of a problem , but if I want to run it on various machines that may
have different DATE setting it won't work.

The software is written in vb.net
Any help would be appreciated.
Cheers
Rob

 

[Cor Ligthert]

Rob,

I would in your situation not trying to make my SQL string in the way as you
do it, especially not with dates.

Internally in Microsoft systems are used USA style dates. Which makes it
difficult for most of us, however when we know, not that big problem because
it is in the newer systems consequent. However it means that you should
avoid as much as possible to convert dates to strings (only for presentation
purposses)

When you use the commandparameters and not try to convert dates to strings,
the problems are mostly gone.

http://msdn.microsoft.com/library/d...dataoledboledbcommandclassparameterstopic.asp

Keep in mind that for OleDB the sequence of adding has to be the same as in
the Selectstring.

I hope this helps?

Cor

 

[seegoon]

Rob,

I would in your situation not trying to make my SQL string in the way as you
do it, especially not with dates.

Internally in Microsoft systems are used USA style dates. Which makes it
difficult for most of us, however when we know, not that big problem because
it is in the newer systems consequent. However it means that you should
avoid as much as possible to convert dates to strings (only for presentation
purposses)

When you use the commandparameters and not try to convert dates to strings,
the problems are mostly gone.

http://msdn.microsoft.com/library/d...dataoledboledbcommandclassparameterstopic.asp

Keep in mind that for OleDB the sequence of adding has to be the same as in
the Selectstring.

I hope this helps?

Cor

Thanks for the help.
I've got it sorted now. Probably not the best method, but it seems to
work.
I reading the system date into a string. Converting it to M,d,yyyy
format with the FORMAT command ,
and then converting back to a date type with CDATE.
Seems to be doing the job fine.
Thanks
Rob

 

[seegoon]

Rob,

I would in your situation not trying to make my SQL string in the way as you
do it, especially not with dates.

Internally in Microsoft systems are used USA style dates. Which makes it
difficult for most of us, however when we know, not that big problem because
it is in the newer systems consequent. However it means that you should
avoid as much as possible to convert dates to strings (only for presentation
purposses)

When you use the commandparameters and not try to convert dates to strings,
the problems are mostly gone.

http://msdn.microsoft.com/library/d...dataoledboledbcommandclassparameterstopic.asp

Keep in mind that for OleDB the sequence of adding has to be the same as in
the Selectstring.

I hope this helps?

Cor

Thanks for the help.
I've got it sorted now. Probably not the best method, but it seems to
work.
I reading the system date into a string. Converting it to M,d,yyyy
format with the FORMAT command ,
and then converting back to a date type with CDATE.
Seems to be doing the job fine.
Thanks
Rob