Solved

Help with TDateTime and SQL

Posted on 2001-06-26
14
175 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
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
 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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