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]