Help with TDateTime and SQL

use parameters like

insert into table(id,date,color) select 2,:ADate,color from table2 where color='yellow'

and use a code like

query1.parambyname('ADate').AsDateTime := Now;
query1.ExecSQL;

meikl ;-)

[Error] Nusse.pas(3756): Undeclared identifier: 'parambyname'


:-(

? which components do you use for your sql-statement
(my sample is for tquery)

TADOQuery

i don't use ado,
but there is also a possibility to use parameters
(no sample available by me->see at the helpfile about tadoquery and paramaters)

but i'm sure someother expert can show this

alternative, depending on the database u use, u could use a database-dependent stringtodate function for this
(see you sql function reference of your databasesystem), but this may fail in some cases, specially, if the regionalsettings of the client and the databaseserver are different

meikl ;-)

This is not a nice solution, I prefer the parameter way of passing dates (that is what I do), but if it isn't working, pass dates in US date format surrounded by '#':

WHERE MyDate = #mm/dd/yyyy#

J.

jsweby,

can you show how to use parameters with a tadoquery for pucko?

kretzschmar, I'd love to, but I don't have or use ADO either! Your original solution would have been mine.

:)

J.

hu, someoneelse, which don't use ado, i'm impressed :-))
(guessed i'm the only one, who avoids ado)

well, maybe i could work out this this evening, if no one other provides a sample

meikl ;-)

it seems like I have:

Query1.Parameters.ParamByName

But I have to create the Parameters and cant find out how.
 

Are you sure you have to create the parameters. As long as you have a parameter in your query, e.g.

SELECT * FROM Table1 WHERE MyDate = :sDate

Then add:

Query1.Parameters.ParamByName('sDate').AsDateTime := Now;

Then most dataset components are automatically of the parameter.

Then again, you're using the components, I'm not, so I guess I can't know any better...

J.

It seems like you have to set the DataType of the parameter like below. .AsDateTime does not exist for
ParamByName


This:
Query1.Parameters[0].Name:='ThisDateTime';
Query1.Parameters[0].DataType:=ftDateTime;
Query1.Parameters.ParamByName('ThisDateTime').Value:= Now;

Gives me Listindex out of bounds so I guess that I have to
add the parameters.


I tried:
Query1.Parameters.CreateParameter('ThisDateTime',ftDateTime,pdInput,0,Now);

But it says that parameter is inproperly delcared or something like that.

Just a thought - why don't you use the built in SQL function to pass through the current date time for example

insert into table(id,date,color) select 2,NOW(),color from table2 where color='yellow'

I don't know what database you are using but there should be some such function on your database.

Andrew