Solved

Help with TDateTime and SQL

Posted on 2001-06-26
14
172 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
Comment Utility
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
Comment Utility
[Error] Nusse.pas(3756): Undeclared identifier: 'parambyname'


:-(
0
 
LVL 27

Expert Comment

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

Author Comment

by:pucko
Comment Utility
TADOQuery
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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
Comment Utility
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
Comment Utility
jsweby,

can you show how to use parameters with a tadoquery for pucko?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Expert Comment

by:jsweby
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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 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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

15 Experts available now in Live!

Get 1:1 Help Now