Prev: Debug VB OCX and DLL
Next: COM Error 80110428 occurred
From: Bob Butler on 3 Jun 2007 09:03 "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 3 Jun 2007 13:55 "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 5 Jun 2007 22:12 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 5 Jun 2007 22:17
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 |