Off-topic tip: My new open-source calendar component for ASP.NET called DayPilot was just released:

• It has an interface similar to Outlook.
• It shows events a single day. Support for more days is planned.
• There is an online demo available.
• It's released under Apache Software License 2.0. Flexible enough even for commercial projects.
• It has a Visual Studio .NET design-time support.

You can download the sources and binaries.

Some time ago I was writing about date and time calculations. Now I needed to do something similar - create a TIMESTAMP value from a DATE field by adding some hours, i.e. something like (DAYDATE being a DATE value, BOOKINGSTART being a TIMESTAMP value):

BOOKINGSTART = DAYDATE + 7.0/24.0;

I'm upgrading a resource booking application. The old system recorded only a date value for each booking but the new one uses starting and ending time. I needed to import the values from the old table (RESUSE) and add a standard starting time (9:00) to create the starting TIMESTAMP and add a standard ending time (18:00) to create the ending TIMESTAMP. So I created the following stored procedure.

CREATE PROCEDURE IMPORT_BOOKINGS 
AS
DECLARE VARIABLE ID INTEGER;
DECLARE VARIABLE RESID INTEGER;
DECLARE VARIABLE DAYDATE DATE;
BEGIN

  DELETE FROM BOOKING;

  FOR SELECT ID,
             RESID,
             DAYDATE,
      FROM RESUSE
      INTO :ID,
           :RESID,
           :DAYDATE,
  DO
  BEGIN

  INSERT INTO RESOURCEBOOKING (
    ID,
    RESOURCEID,
    BOOKSTART,
    BOOKEND)
  VALUES (
    :ID,
    :RESID,
    :DAYDATE + 9/24,
    :DAYDATE + 18/24
  );

  END
END

It didn't work. So I found the first mistake: "9/24" produces an integer. So I updated it to:

CREATE PROCEDURE IMPORT_BOOKINGS 
AS
DECLARE VARIABLE ID INTEGER;
DECLARE VARIABLE RESID INTEGER;
DECLARE VARIABLE DAYDATE DATE;
BEGIN

  DELETE FROM BOOKING;

  FOR SELECT ID,
             RESID,
             DAYDATE,
      FROM RESUSE
      INTO :ID,
           :RESID,
           :DAYDATE,
  DO
  BEGIN

  INSERT INTO RESOURCEBOOKING (
    ID,
    RESOURCEID,
    BOOKSTART,
    BOOKEND)
  VALUES (
    :ID,
    :RESID,
    :DAYDATE + 9.0/24.0,
    :DAYDATE + 18.0/24.0
  );

  END
END

But this didn't work either. So I had to investigate how Firebird handles this. The problem is that when you add a number to a DATE value Firebird only takes the integral fraction of the number and creates a new DATE value. To create a TIMESTAMP value you must add TIME value, like this:

CREATE PROCEDURE IMPORT_BOOKINGS 
AS
DECLARE VARIABLE ID INTEGER;
DECLARE VARIABLE RESID INTEGER;
DECLARE VARIABLE DAYDATE DATE;
BEGIN

  DELETE FROM BOOKING;

  FOR SELECT ID,
             RESID,
             DAYDATE,
      FROM RESUSE
      INTO :ID,
           :RESID,
           :DAYDATE,
  DO
  BEGIN

  INSERT INTO RESOURCEBOOKING (
    ID,
    RESOURCEID,
    BOOKSTART,
    BOOKEND)
  VALUES (
    :ID,
    :RESID,
    :DAYDATE + CAST('7:0' AS TIME),
    :DAYDATE + CAST('15:0' AS TIME)
  );

  END
END

And that finally worked!

The first official build that works with the final versions of Visual Studio 2005 was released yesterday.

What's the most interesting:

The new DDEX Provider allows showing Firebird in Database Explorer/Data Connections. For installing it you should follow the ReadmeDDEX.txt file in the FirebirdClient installation directory. The installation requires modification of machine.config and adding some record to the system registry. Unfortunately I wasn't able to get it working in Visual Web Developer 2005.

New features:

• New GDS implementation for the Firebird/Fyracle External Proceudre engine implementation:Added a new namespace FirebirdSql.Data.StoredProcedureEngine with two new classes (FbResultSet, FbTriggetContext).

Other changes:

• Cleanup.
• Updated to build using .NET 2.0 RTM.
• Improved connection pooling implementation.
• Changes on FbConnectionStringBuilder class to use the new FbServerType enumeration.

Download:

• Download section