[SOLVED] How to use 'NOW' in datetime field?

[egsuh]

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][+][-]

1. insert into atable (c_date) values ('now');
2.  

Field c_date is a date_time type.

But, following Lazarus code does not work.

Code: Pascal  [Select][+][-]

1. qr.SQL.text := 'insert into atable (c_date) values (:c_date);';
2. qr.params[0].AsString := 'NOW';
3. qr.execsql;
4.  

Any solution I can use within Lazarus/FPC?

[MichaelBM]

Code: Pascal  [Select][+][-]

1.    
2. qr.SQL.text := 'insert into atable (c_date) values (:c_date);';
3. qr.params[0].AsDateTime := Now;
4. qr.execsql;
5.  

     

[egsuh]

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

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

[Thaddy]

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

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

Posts crossed Rik, totally agree...

[egsuh]

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

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.

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]

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