Re: {Disarmed} [firebird-support] How do I convert a timestamp to a string from within a stored procedure?

[SoftTech]

For anyone else who needs to do this:

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 > 12) THEN
        iHour = (iHour-12);

      IF (iHour = 0) THEN
        sHour = '12';
      ELSE IF ((iHour > 0) AND (iHour < 11)) THEN
        sHour = '0' || iHour;
      ELSE IF (iHour > 12) THEN
        sHour = (iHour-12);
      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 ;  ^^


  ----- Original Message ----- 
  From: SoftTech 
  To: firebird-support@yahoogroups.com 
  Sent: Thursday, January 26, 2012 5:50 AM
  Subject: {Disarmed} [firebird-support] How do I convert a timestamp to a 
string from within a stored procedure?


    
  Greetings All,

  Does anyone know how I can accomplish this from within a stored procedure?

  I need to convert a timestamp 01/26/2012 05:38:32am to a string 
  01262012053832.

  Will actually be using CURRENT_TIMESTAMP within the stored procedure.

  Still using Firebird 1.5.3 (Hope to convert down the road)

  Thanks to all who respond.

  Mike 



  
  -- 
  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]