Help with TDateTime and SQL

I like to do a:

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

date is a dateTime field. How can I do that,

XX:=DateTimeToStr(Now);
dont work.
 
LVL 1
puckoAsked:
Who is Participating?
 
kretzschmarConnect With a Mentor Commented:
just set the paramcheck-property to true,
the parameter should now automatically added
after u insert your sql statement

the it should work with
Query1.Parameters.ParamByName('ThisDateTime').DataType := ftDateTime;
Query1.Parameters.ParamByName('ThisDateTime').Value:= Now;

well not tested (can't test no ado installed)

meikl
0
 
kretzschmarCommented:
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 ;-)
0
 
puckoAuthor Commented:
[Error] Nusse.pas(3756): Undeclared identifier: 'parambyname'


:-(
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
kretzschmarCommented:
? which components do you use for your sql-statement
(my sample is for tquery)
0
 
puckoAuthor Commented:
TADOQuery
0
 
kretzschmarCommented:
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 ;-)
0
 
jswebyCommented:
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.
0
 
kretzschmarCommented:
jsweby,

can you show how to use parameters with a tadoquery for pucko?
0
 
jswebyCommented:
kretzschmar, I'd love to, but I don't have or use ADO either! Your original solution would have been mine.

:)

J.
0
 
kretzschmarCommented:
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 ;-)
0
 
puckoAuthor Commented:
it seems like I have:

Query1.Parameters.ParamByName

But I have to create the Parameters and cant find out how.
 
0
 
jswebyCommented:
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.
0
 
puckoAuthor Commented:
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.
0
 
bigpropCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.