[SOLVED] How to use 'NOW' in datetime field?
lazarus / 16-Databases

----------------------------------------------------------------------
egsuh · 2020-02-22 12:38

Hi,

I'm using Firebird 3.0 with Lazarus 2.0.4 on Windows 10.

Following SQL has no problem if executed directly.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i insert into
atable (c_date) values ('now');  Field c_date is a date_time type.

But, following Lazarus code does not work.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i qr.SQL.text
:= 'insert into atable (c_date) values (:c_date);';
qr.params[0].AsString := 'NOW'; qr.execsql; 
Any solution I can use within Lazarus/FPC?

----------------------------------------------------------------------
MichaelBM · 2020-02-22 12:43

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
qr.SQL.text := 'insert into atable (c_date) values
(:c_date);';qr.params[0].AsDateTime := Now;qr.execsql;

----------------------------------------------------------------------
egsuh · 2020-02-24 12:06

I wanted to use all parameters as string type.
So, I changed to:

qr.params[0].AsString := DateToStr(now);

----------------------------------------------------------------------
Thaddy · 2020-02-24 12:11

Quote from: egsuh on February 24, 2020, 01:06:01 pmI wanted to use all
parameters as string type.
So, I changed to:

qr.params[0].AsString := DateToStr(now);
Are you aware of the sort complexities of storing dates as string? at
least use a format that uses year/month/date.
If your database supports it, use a different type or at least make
sure that order is respected.

A date string is a display format, not a storage format. That is a
basic error.

----------------------------------------------------------------------
rvk · 2020-02-24 12:12

Quote from: egsuh on February 24, 2020, 01:06:01 pmI wanted to use all
parameters as string type.
So, I changed to:
qr.params[0].AsString := DateToStr(now);
In that case I would make sure you end up with '2020-02-24' as string
and NOT '02-24-2020'.
Otherwise you might have unexpected results.

----------------------------------------------------------------------
Thaddy · 2020-02-24 12:17

Posts crossed Rik, totally agree...

----------------------------------------------------------------------
egsuh · 2020-02-25 04:54

@Thaddy, rvk:

Thank you for your comments.

In the database table (Firebird), the field type is datetime, so the
data themselves will be saved as some kind of float number.

Using string type is just for convenience in the construction of SQL
texts, and conversions are done only by the system.

----------------------------------------------------------------------
rvk · 2020-02-25 06:18

Quote from: egsuh on February 25, 2020, 05:54:17 amIn the database
table (Firebird), the field type is datetime, so the data themselves
will be saved as some kind of float number.

Using string type is just for convenience in the construction of SQL
texts, and conversions are done only by the system.
That's not what we meant.

Do you know the difference between 02-03-2020 and 03-02-2020?
I can tell you, Firebird doesn't !
(And halve the world will disagree with you about what's what)

Depending on the system you run this on (or the server is installed
on) the date will be in februari or march. And I can guarantee you
this will go wrong someday.

If you use 2020-03-02 as string, you are sure to be in march, and
Firebird knows that.
So.. Never, ever use dd-mm-yyyy or mm-dd-yyyy format if you are
dealing with databases.
Use yyyy-mm-dd

So... did you check what DateToStr(now) gave you back?

----------------------------------------------------------------------
egsuh · 2020-02-25 07:44

@rvk

Really appreciate your advise.  

Up to now, we have no problem in saving and retrieving the date as we
expected.  I do not type in the date string --- instead I pick it up
from date picker, etc. 

I had no problem up to now because default formats are yyyy-mm-dd in
our PCs, which is not guaranteed on other PCs. So, your advice reminds
me that I have to set the date format within my application to yyyy-
mm-dd if possible. I'll check whether it is possible in Lazarus / Free
Pascal.