We help IT Professionals succeed at work.

Firebird insert date time in a field.

Alex
Alex asked
on

I have a problem about how to insert the current date time when i insert a new record in my database(Firebird).

I set the field of the table for the date as a Date field.

I use this code to insert the date and time to this field with some other data.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
        IBQuery1.SQL.Clear;
        IBQuery1.SQL.Append('INSERT INTO TOTALS (bc,br,idpl,bcdate) VALUES ('+quotedstr(edit1.Text)+','+floattostr(e)+','+quotedstr(label9.Caption)+',NOW)');
        IBQuery1.ExecSQL;

I try to use the NOW but i get an error can you help me how to insert the current date time.

Thanks in advance!
Comment
Watch Question

Engineer
CERTIFIED EXPERT
Commented:
Because you are inserting the date via SQL you are generating rather than via parameters, you need to format it correctly.

IBQuery1.SQL.Append('INSERT INTO TOTALS (bc,br,idpl,bcdate) VALUES ('+quotedstr(edit1.Text)+','+floattostr(e)+','+quotedstr(label9.Caption)+',' + quotedStr( formatDateTime( 'yyyy-mm-dd', now)) + ')');
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
to get current date on interbase server you can try:

  select current_date from rdb$database
or
  select cast('today' as Date)

so your sql statement might look something like this

INSERT INTO TOTALS (bc,br,idpl,bcdate) VALUES ('+quotedstr(edit1.Text)+','+floattostr(e)+','+quotedstr(label9.Caption)+', select cast('today' as Date))'

this should also work on firebird but you must check yourself

ziolko.
AlexSoftware Engineer
CERTIFIED EXPERT

Author

Commented:
thanks my friend.

I try this but i get an error.

sql error - 804

it has to do with the decimal and the thousand seperator.What do you suggest me?
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
try also without select:
INSERT INTO TOTALS (bc,br,idpl,bcdate) VALUES ('+quotedstr(edit1.Text)+','+floattostr(e)+','+quotedstr(label9.Caption)+',  cast('today' as Date))'

ziolko.
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
>>it has to do with the decimal and the thousand seperator.What do you suggest me?

I'll suggest that you check if DecimalSeparator = '.'

DecimalSeparator is defined in sysUtils so you might need to add this to uses clausule

ziolko.

Commented:
try with getdate function:
INSERT INTO TOTALS (bc,br,idpl,bcdate) VALUES (quotedstr(edit1.Text)+','+floattostr(e)+','+quotedstr(label9.Caption)+',  getdate())
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
bokist are you sure that getdate() is supported by interbase/firebird?

ziolko.

Commented:
No I'm not.
I never use that DB, but it's worth to try.
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
well it's not supported at least wasn't in last version I worked on before:)

ziolko.
Mike LittlewoodEngineer
CERTIFIED EXPERT

Commented:
Why dont you just format the date into a string like I mentioned before then.
AlexSoftware Engineer
CERTIFIED EXPERT

Author

Commented:
thank you all,

I'll give a try tomorrow at work and i'll post back.
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
@mikelittlewood
I'm sure ForamtDateTime() would work but I've had some bad experiences with datetime formats accepted by db engines not to mention that Now returns current datetime but on local machine not server on which db is running, that's why I tend to use built-in functions to insert current datetime.


@alex_code
this is probably not solution for this specific problem, but in future consider using stored procedures for updating/inserting. for instance in this case you could create stored proc with 3 params and set datetime from within stored proc.

or even simpler way use default values:


CREATE TABLE mytable (

    my_time TIMESTAMP DEFAULT 'NOW' NOT NULL,
    my_date TIMESTAMP DEFAULT 'TODAY' NOT NULL

);

ziolko.
AlexSoftware Engineer
CERTIFIED EXPERT

Author

Commented:
thanks all for your posts!

mikelittlewood's solution was perfect i add only the time to the format and everything it's ok!


IBQuery1.SQL.Append('INSERT INTO TOTALS (bc,br,idpl,bcdate) VALUES ('+quotedstr(edit1.Text)+','+floattostr(e)+','+quotedstr(label9.Caption)+',' + quotedStr( formatDateTime( 'yyyy-mm-dd hh:mm', now)) + ')');

Thank you mikelittlewood ;)