Solved

Help with TDateTime and SQL

Posted on 2001-06-26
14
176 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

735 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