Firebird insert date time in a field.


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!
LVL 9
AlexSoftware EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mikelittlewoodCommented:
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)) + ')');
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ziolkoCommented:
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.
0
AlexSoftware EngineerAuthor 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?
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

ziolkoCommented:
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.
0
ziolkoCommented:
>>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.
0
bokistCommented:
try with getdate function:
INSERT INTO TOTALS (bc,br,idpl,bcdate) VALUES (quotedstr(edit1.Text)+','+floattostr(e)+','+quotedstr(label9.Caption)+',  getdate())
0
ziolkoCommented:
bokist are you sure that getdate() is supported by interbase/firebird?

ziolko.
0
bokistCommented:
No I'm not.
I never use that DB, but it's worth to try.
0
ziolkoCommented:
well it's not supported at least wasn't in last version I worked on before:)

ziolko.
0
mikelittlewoodCommented:
Why dont you just format the date into a string like I mentioned before then.
0
AlexSoftware EngineerAuthor Commented:
thank you all,

I'll give a try tomorrow at work and i'll post back.
0
ziolkoCommented:
@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.
0
AlexSoftware EngineerAuthor 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 ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.