Solved

Help with TDateTime and SQL

Posted on 2001-06-26
14
173 Views
Last Modified: 2010-04-06
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.
 
0
Comment
Question by:pucko
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6226735
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
 
LVL 1

Author Comment

by:pucko
ID: 6226754
[Error] Nusse.pas(3756): Undeclared identifier: 'parambyname'


:-(
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6226775
? which components do you use for your sql-statement
(my sample is for tquery)
0
 
LVL 1

Author Comment

by:pucko
ID: 6226785
TADOQuery
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6226800
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
 
LVL 4

Expert Comment

by:jsweby
ID: 6226829
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6226841
jsweby,

can you show how to use parameters with a tadoquery for pucko?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

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

:)

J.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6226871
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
 
LVL 1

Author Comment

by:pucko
ID: 6226879
it seems like I have:

Query1.Parameters.ParamByName

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

Expert Comment

by:jsweby
ID: 6226890
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
 
LVL 1

Author Comment

by:pucko
ID: 6226927
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
 
LVL 27

Accepted Solution

by:
kretzschmar earned 50 total points
ID: 6228707
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
 

Expert Comment

by:bigprop
ID: 6252095
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need Delphi function to get Youtube video title 5 225
creating threads in delphi 1 82
Performance of SQL statement 37 97
Adoquery sql  left join does not work 25 80
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now