Ooh … Begitu! I see, I see …

Icon

Setelah kelayapan di internet, cari-cari info dari sana-sini dan bongkar pasang tips (terutama programming), akhirnya baru sadar … ternyata begitu caranya … [all about tricks and tips I have found during my work as programmer.]

Date Time simple tips for Firebird store procedure

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. 🙂

Advertisements

Filed under: Firebird

5 Responses

  1. […] Date Time simple tips for Firebird store procedure okay, first … I would like to admit that I’m quite a newbie in Firebird database. I used MySQL back then […] […]

  2. baliazura says:

    I use computer and my place work use proxy so I have problem to load thunderbird or my network protect with firewall. any one can help me?

    hi baliazura, I’m not sure if I can help, but you can check out my new entry at Thinderbird proxy setting?? page. hope it helps. thank you for visiting my blog. 🙂

    :: Tukang Bongkar Pasang ::

  3. golfbreak says:

    Interesting article, thank you.

  4. digital-glamour says:

    hey great stuff

  5. sandrar says:

    Hi! I was surfing and found your blog post… nice! I love your blog. 🙂 Cheers! Sandra. R.

    hi! thanks for coming 🙂
    cheers!

    :: Bongkar Pasang ::

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives

%d bloggers like this: