RE: [firebird-support] How do I convert a timestamp to a string from within a stored procedure?

[SoftTech]

Greetings Svein,

Great catch...

I'm creating a Metro2 formatted document for reporting to credit bureaus and 
accounting to their specs:
Time Stamp Contains date and time of actual account information update.  Format 
is MMDDYYYYHHMMSS for character date.

I should of course have use military time.

So here is the corrected procedure:

SET TERM  ^^ ;
CREATE PROCEDURE SPS_CR_STRING_TIMESTAMP (
  V_TIMESTAMP TimeStamp)
 returns (
  STR_TIMESTAMP VarChar(14))
AS
/*
  Author   : Michael G. Tuttle, Software Technologies, Inc.
  Date     : 1/26/2012 7:48:03 AM
  Purpose  : Used for credit reporting - Takes a timestamp 1/26/2012 7:48:03 AM 
and converts it to a string '01262012074803'
*/
DECLARE VARIABLE sMonth VarChar(2);
DECLARE VARIABLE sDay VarChar(2);
DECLARE VARIABLE sYear VarChar(4);
DECLARE VARIABLE iHour SmallInt;
DECLARE VARIABLE iMinute SmallInt;
DECLARE VARIABLE iSecond SmallInt;
DECLARE VARIABLE sHour Char(2);
DECLARE VARIABLE sMinute Char(2);
DECLARE VARIABLE sSecond Char(2);
begin
  IF (V_TIMESTAMP IS NULL) THEN
    STR_TIMESTAMP = '00000000000000';
  ELSE
    BEGIN
      sMonth = EXTRACT(MONTH FROM V_TIMESTAMP);
      IF (F_STRINGLENGTH(sMonth) = 1) THEN
        sMonth = '0' || sMonth;

      sDay = EXTRACT(DAY FROM V_TIMESTAMP);
      IF (F_STRINGLENGTH(sDay) = 1) THEN
        sDay = '0' || sDay;

      sYear = EXTRACT(YEAR FROM V_TIMESTAMP);

      /* Time */
      iHour = EXTRACT(HOUR FROM V_TIMESTAMP);
      iMinute = EXTRACT(MINUTE FROM V_TIMESTAMP);
      iSecond = EXTRACT(SECOND FROM V_TIMESTAMP);

      IF ((iHour >= 0) AND (iHour < 10)) THEN
        sHour = '0' || iHour;
      ELSE
        sHour = iHour;

      IF (iMinute < 10) THEN
        sMinute = '0' || iMinute;
      ELSE
        sMinute = iMinute;

      IF (iSecond < 10) THEN
        sSecond = '0' || iSecond;
      ELSE
        sSecond = iSecond;

      STR_TIMESTAMP = sMonth || sDay || sYear || sHour || sMinute || sSecond;
    END
end
 ^^
SET TERM ;  ^^

Thanks again Swein,

Mike
  ----- Original Message ----- 
  From: Svein Erling Tysvær 
  To: 'firebird-support@yahoogroups.com' 
  Sent: Thursday, January 26, 2012 8:19 AM
  Subject: {Disarmed} RE: [firebird-support] How do I convert a timestamp to a 
string from within a stored procedure?


    
  Hi Michael!

  You didn't ask, but there are a couple of things in your procedure that makes 
me wonder whether it is actually what you want (sorted in order of importance):

  > ELSE IF ((iHour > 0) AND (iHour < 11)) THEN
  > sHour = '0' || iHour;

  Probably you want iHour < 10 rather than 11?

  > IF (iHour > 12) THEN
  > iHour = (iHour-12);

  This basically means that you will be unable to differentiate between AM and 
PM and you cannot tell which of two timestamps come first (don't you care 
whether one entry is 6am or 6pm?).

  > STR_TIMESTAMP = sMonth || sDay || sYear || sHour || sMinute || sSecond;

  This will work OK as long as your application is local and you'll never have 
to sort or search depending on STR_TIMESTAMP. Internationally, several 
countries generally use DMY rather than MDY format, and if you avoid both of 
these and use YMD format, you also get a format that is easy to use for sorting 
and searching (well, not if you use a 12 hour clock).

  Set


  
  -- 
  This message has been scanned for viruses and 
  dangerous content by MailScanner, and is 
  believed to be clean. 

[Non-text portions of this message have been removed]