Recent

Author Topic: [SOLVED] How to use 'NOW' in datetime field?  (Read 470 times)

egsuh

  • Sr. Member
  • ****
  • Posts: 358
[SOLVED] How to use 'NOW' in datetime field?
« on: February 22, 2020, 01:38:25 pm »
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?
« Last Edit: February 24, 2020, 01:04:27 pm by egsuh »

MichaelBM

  • New Member
  • *
  • Posts: 33
Re: How to use 'NOW' in datetime field?
« Reply #1 on: February 22, 2020, 01:43:47 pm »
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.  
     
Development Tool: Lazarus 2.0.6 + FPC 3.0.4
Database: Firebird 2.5.7
Operating System: Windows 10 Pro 64-bit

egsuh

  • Sr. Member
  • ****
  • Posts: 358
Re: [SOLVED] How to use 'NOW' in datetime field?
« Reply #2 on: February 24, 2020, 01:06:01 pm »
I wanted to use all parameters as string type.
So, I changed to:

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




Thaddy

  • Hero Member
  • *****
  • Posts: 10099
Re: [SOLVED] How to use 'NOW' in datetime field?
« Reply #3 on: February 24, 2020, 01:11:37 pm »
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.
« Last Edit: February 24, 2020, 01:16:42 pm by Thaddy »
I am more like donkey than shrek

rvk

  • Hero Member
  • *****
  • Posts: 3988
Re: [SOLVED] How to use 'NOW' in datetime field?
« Reply #4 on: February 24, 2020, 01:12:31 pm »
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

  • Hero Member
  • *****
  • Posts: 10099
Re: [SOLVED] How to use 'NOW' in datetime field?
« Reply #5 on: February 24, 2020, 01:17:33 pm »
Posts crossed Rik, totally agree...
I am more like donkey than shrek

egsuh

  • Sr. Member
  • ****
  • Posts: 358
Re: [SOLVED] How to use 'NOW' in datetime field?
« Reply #6 on: February 25, 2020, 05:54:17 am »
@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

  • Hero Member
  • *****
  • Posts: 3988
Re: [SOLVED] How to use 'NOW' in datetime field?
« Reply #7 on: February 25, 2020, 07:18:40 am »
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?
« Last Edit: February 25, 2020, 07:25:47 am by rvk »

egsuh

  • Sr. Member
  • ****
  • Posts: 358
Re: [SOLVED] How to use 'NOW' in datetime field?
« Reply #8 on: February 25, 2020, 08:44:43 am »
@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.   

 

TinyPortal © 2005-2018