From: Bob Butler on
"Luiz Horacio" <lhoracio(a)iname.com> wrote in message
news:ekqUm1dpHHA.588(a)TK2MSFTNGP06.phx.gbl...
> Hi Jeff,
>
>> If this is a true date field then data is NOT stored with ANY format.
>> Formats are for display, which is to say, formats are for strings.
>> Numbers are numbers are numbers are numbers.
>
> It is a Date/time field. I can understand that numbers are numbers, and
> that it is not stored in any format (never thought of this...), but since
> SQL query returns those numbers (or whatever is stored in DB) in that
> specific format,

The query returns the raw data; it gets formatted when you look at it (sort
of a Schrodinger's datetime value)

> and queries need that specific format to work,

Queries need to be sent using an unambiguous format because the database
engine has to be able to convert the text string you are sending to the
internal format to do the comparisons.

> the specific displayed format is what matters. Am I wrong?

When you get the values back you can display them in any format you want to
use.

From: Steve Gerrard on

"Luiz Horacio" <lhoracio(a)iname.com> wrote in message
news:ekqUm1dpHHA.588(a)TK2MSFTNGP06.phx.gbl...
> Hi Jeff,
>
>> If this is a true date field then data is NOT stored with ANY format. Formats
>> are for display, which is to say, formats are for strings. Numbers are
>> numbers are numbers are numbers.
>
> It is a Date/time field. I can understand that numbers are numbers, and that
> it is not stored in any format (never thought of this...), but since SQL query
> returns those numbers (or whatever is stored in DB) in that specific format,
> and queries need that specific format to work, the specific displayed format
> is what matters. Am I wrong?
>
>

Actually the best way to do date queries, if possible, is to use parameters.
Parameters allow you to pass the where clause limits as actual dates (numbers),
rather than as formatted text. Besides eliminating issues around date/time
formats, it has the added benefits of allowing better optimization on the db
server end, and it closes a security hole by eliminating an opportunity for SQL
injection.

To run them in VB using ADO, you need to create a Command object, and append the
needed parameters to its Parameters collection - so it can be a bit more work up
front. I work with an Oracle server, and the syntax is different for SQL server,
so take this with a grain of salt. There are others here who can help you if you
want to pursue it. But basically, once the parameters are setup correctly, your
SQL statement would look something like this:

...WHERE AdmDate BETWEEEN @D1 And @D2..."
@D1 and @D2 would be two parameters you have set up in the Command. You then set
those to the desired values:
MyCmd.Parameters("@D1").Value = SomeDateTime
MyCmd.Parameters("@D2").Value = AnotherDateTime

Then you run the command, and use the result as you wish. Again, I probably have
the syntax wrong for doing this on SQL server, but it is along these lines. I
think the @ sign is correct, anyway :)


From: Luiz Horacio on
Hi Bob,

> The query returns the raw data; it gets formatted when you look at it
> (sort of a Schrodinger's datetime value)

:))

> Queries need to be sent using an unambiguous format because the database
> engine has to be able to convert the text string you are sending to the
> internal format to do the comparisons.

Ok, I see.

> When you get the values back you can display them in any format you want
> to use.

Ok, I get it.

Thanks for your patience.


--
Luiz Horacio
lhoracio(a)imadi.com.br


From: Luiz Horacio on
Hi Steve,


> Actually the best way to do date queries, if possible, is to use
> parameters.

I've seen this working sometimes, and found it wonderful.

But I'm not, in fact, a VB programer... As I use to say, I can do some
tricks with VB, but that's all. I think this is far beyond my current
knowledge of VB & DB.

Anyway, thanks for the sugestion.


Thanks,


--
Luiz Horacio


First  |  Prev  | 
Pages: 1 2 3 4 5 6
Prev: Debug VB OCX and DLL
Next: COM Error 80110428 occurred