Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Setting date to Now() in paramterized query


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 7 - Last Post: Nov 9, 2017 9:39 AM Last Post By: Mark Williams
Mark Williams

Posts: 120
Registered: 5/8/10
Setting date to Now() in paramterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 9, 2017 3:43 AM
I am trying to run a query which sets a datetime field to the current datetime for the server. Code is a follows:

FDQuery1.sql.text:='INSERT INTO log_in SET last_log_in= :LogIn, user_id= :UserID ON DUPLICATE KEY UPDATE last_log_in= :LogIn';
FDQuery1.ParamByName('LogIn').AsString:='Now()';
FDQuery1.ParamByName('UserID').AsString:=MainUser.id;
FDQuery1.execute;

I am getting an incorrect datetime value error. How do you use Now() in a paramterized query?
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Setting date to Now() in paramterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 9, 2017 7:03 AM   in response to: Mark Williams in response to: Mark Williams
Mark Williams wrote:

I am trying to run a query which sets a datetime field to the current
datetime for the server. Code is a follows:

FDQuery1.sql.text:='INSERT INTO log_in SET last_log_in= :LogIn,
user_id= :UserID ON DUPLICATE KEY UPDATE last_log_in= :LogIn';
FDQuery1.ParamByName('LogIn').AsString:='Now()';
FDQuery1.ParamByName('UserID').AsString:=MainUser.id;
FDQuery1.execute;

I am getting an incorrect datetime value error. How do you use Now()
in a paramterized query?

In this example you're assigning string constant Now() to the parametar
Login. What is the type of the parameter? Most probably you're after:

FDQuery1.ParamByName('LogIn').AsDateTime:=now;

Robert Triest

Posts: 687
Registered: 3/24/05
Re: Setting date to Now() in paramterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 9, 2017 7:15 AM   in response to: Mark Williams in response to: Mark Williams
Yes, what Lajos wrote

or if your LoginField is a string:

FDQuery1.ParamByName('LogIn').AsString:=DateTimeToStr(Now);
Mark Williams

Posts: 120
Registered: 5/8/10
Re: Setting date to Now() in paramterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 9, 2017 7:44 AM   in response to: Robert Triest in response to: Robert Triest
Robert Triest wrote:
Yes, what Lajos wrote

or if your LoginField is a string:

FDQuery1.ParamByName('LogIn').AsString:=DateTimeToStr(Now);

In both examples, you are setting the ;LogIn' field's value to the current date time of the client computer. This is what I am specifically trying to avoid.

What I wish to do is the following query, but paramaterized.

INSERT INTO log_in SET last_log_in= Now(), user_id= :UserID ON DUPLICATE KEY UPDATE last_log_in=Now()

As you probably know,"Now()" is an SQL function which returns the server date not the client date. My question is, how do I set a param value to the SQL Now() function?
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Setting date to Now() in paramterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 9, 2017 8:10 AM   in response to: Mark Williams in response to: Mark Williams
This depends on your database you use.
In MSSQL it's (getdate())

Best option is to define on your table field a default value or binding.
When the insert is done with your Insert Query, the database will fill in this
datetime for you. In MSSQL you have to fill in (getdate())

As you probably know,"Now()" is an SQL function which returns the server date not the client date.
My question is, how do I set a param value to the SQL Now() function?

Then Now() is a constant of the database and not a var parameter.
Mark Williams

Posts: 120
Registered: 5/8/10
Re: Setting date to Now() in paramterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 9, 2017 9:37 AM   in response to: Robert Triest in response to: Robert Triest
Best option is to define on your table field a default value or binding.
When the insert is done with your Insert Query, the database will fill in this
datetime for you. In MSSQL you have to fill in (getdate())

That would work on Insert but no on Duplicate Key update. The table is keeping a single record of the last time a user logged on. First time there will be an insert, every time after it will be an update so default won't work or am I missing something
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Setting date to Now() in paramterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 9, 2017 8:19 AM   in response to: Mark Williams in response to: Mark Williams
Mark Williams wrote:

Robert Triest wrote:
Yes, what Lajos wrote

or if your LoginField is a string:

FDQuery1.ParamByName('LogIn').AsString:=DateTimeToStr(Now);

In both examples, you are setting the ;LogIn' field's value to the
current date time of the client computer. This is what I am
specifically trying to avoid.

What I wish to do is the following query, but paramaterized.

INSERT INTO log_in SET last_log_in= Now(), user_id= :UserID ON
DUPLICATE KEY UPDATE last_log_in=Now()

As you probably know,"Now()" is an SQL function which returns the
server date not the client date. My question is, how do I set a param
value to the SQL Now() function?

Just like you wrote make now an sql function.
Mark Williams

Posts: 120
Registered: 5/8/10
Re: Setting date to Now() in paramterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 9, 2017 9:39 AM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Just like you wrote make now an sql function.

Now() is already a function in MySQL. If I understand you correctly, you are saying there is no point in paramterizing the Now() function and on reflection that is probably right.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02