How to insert a date?

I notice that firebird expects the date-format to be MM-DD-YYYY for an insert.
So the following statement will be correct:
insert into my table values ('08-31-2004');

However I want to insert a date with the format 'DD-MM-YYYY' (that's the format my application sends the field to firebird). But when I do:
insert into my table values ('31-08-2004');
I get the error "Overflow occurred during data type conversion". (Which seems logical because 31 is not a valid month).

Is it somehow possible to insert a date with the format DD-MM-YYYY and if yes how?

Thanks in advance, Peter

In addition to this:
Sometimes the date column can be empty, but I still have to do an insert. When I do something like this, I get an error again (unknown token).
insert into table(id,name,date) values (1,'test','');

It works correctly when I do:
insert into table(id,name,date) values (1,'test',null);

However: my stored procedure, which handles the insert-statement, gets an empty string as input variable. How to convert this '' to null in a stored procedure.

Thanks again! Peter

Anyone?
Come one guys; this question cannot be to difficult!
Any help is appreciated!!

Hi,

If your procedure accepts a DATE as the value, it won't allow '' to get accepted. If you're accepting a VARCHAR as the parameter, you could easily check:

CREATE PROCEDURE <name> (..., ..., dateinput varchar(10) )
...
DECLARE VARIABLE mydate DATE;
...
AS
BEGIN
if (dateinput = '')
then mydate = NULL;
else mydate = cast(dateinput as DATE);
INSERT INTO mytable (...) VALUES (..., :mydate);
END

As for the DD-MM-YYYY thing: Firebird accepts several formats, but by using / or - it accepts the MM-DD-YYYY format. I believe you can do DD.MM.YYYY.

The best thing would be to accept a DATE only and on the client side pass a date instead of a string.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server.
Upscene Productions
http://www.upscene.com

Thanks a lot Martijn!!

I didn't know you could use 'cast' in a stored procedure. In fact, I didn't know cast existed at all in firebird.
I read the standard documentation about stored procedures and triggers and it isn't described there. Or maybe I'm not looking at the right place. Can you recommend some documentation where this is described?

I'm using a varchar input variable, because I receive the date fromm an html-form and I want to send this directly to Firebird (after checking on the client side with javascript if it's valid date). My php serverside script is just getting it from html as a string and immediately sending it to Firebird as a string. It might be better though to first change it in a date, before passing it... (or change the dd-mm-yyyy string to mm-dd-yyyy)

Thanks again!
Peter