okay, first … I would like to admit that I’m quite a newbie in Firebird database. I used MySQL back then but few months ago my employer wanted to migrate to Firebird and up till now, we still remain using Firebird 1.5 for certain reasons. quite a headache for me because this one was totally new for me. I decided to put these things up, since some of my friends were facing some similar problems with me and these are the recent info I have collected so far from many searching in the forums out there, and perhaps any of you would like to submit more advice or perhaps drop any useful tutorial URLs in the comments part (and thank you in advance for spending your times doing that).
here we go. this is one of the problems I encountered using Firebird 1.5. I used to cast date format in Delphi into ‘yyyy-mm-dd’ format when I tried to filter any sql selection using date. somehow it worked, but sometimes it didn’t work properly, usually when I used it inside a store procedure script. so I always wondered about the proper way to cast the values into sql date format correctly. and then I accidentally came to Firebird Really Useful (Tips & Techniques) at http://www.firebirdsql.org/index.php?op=useful, and found a really good article about Some Store Procedure to Massage Dates.
thanks to Markus Ostenried who provided the article.
here is the trick to solve the casting into sql date format:
CREATE PROCEDURE Proc_EncodeDate(AYear INTEGER, AMonth INTEGER, ADay INTEGER) RETURNS(Result DATE) AS BEGIN Result = cast(ADay || '.' || AMonth || '.' || AYear as DATE); suspend; END
by the way, should I share this simple tips too, from the same Firebird Really Useful page. just in case you want to know. this is a tips to extract the values for the year, month and day from a date timestamp format.
CREATE PROCEDURE Proc_DecodeDate(ADate TIMESTAMP) RETURNS(AYear INTEGER, AMonth INTEGER, ADay INTEGER) AS BEGIN AYear = extract(Year from ADate); AMonth = extract(Month from ADate); ADay = extract(Day from ADate); suspend; END
and another one from the same author of the tips, I decided to quote it too, simply for those who have been questioning about how to get correct day numbers in a month. here we go:
CREATE PROCEDURE Proc_DaysOfMonth(AYear INTEGER, AMonth INTEGER) RETURNS(Result INTEGER) AS DECLARE VARIABLE WorkDate DATE; BEGIN Result = 31; WHILE (Result > 28 AND WorkDate IS NULL) DO BEGIN EXECUTE PROCEDURE Proc_EncodeDate(AYear, AMonth, Result) RETURNING_VALUES(WorkDate); WHEN ANY DO Result = Result - 1; END END
pretty interesting for simple date procedures, right? 🙂 the source article itself does have great date and time store procedures useful sources. the article even provides the procedures to calculate the German “Feiertage” (feast days). you gotta check them out at http://www.firebirdsql.org/index.php?op=useful&id=ostenried_1. hopefully the URL still works. I believe there are more interesting date and time Firebird sql tips on forums out there, probably Hebrew, Arabian, Chinese or Japanese calendar for a challenge? if you happen to know any links out there to be checked out, don’t hesitate to drop me a link. thank you for reading. 🙂