[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
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
Re: [firebird-support] How do I convert a timestamp to a string from within a stored procedure?
SoftTech wrote: > 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. You could use EXTRACT() to extract day, month, year, hour, etc. and use || operator to concatenate numbers into string. To make sure numbers are zero padded you can use STR_PAD or CASE like this: case when number < 10 then '0'||number else number end I mention CASE because STR_PAD is not available in older versions of FB. Alternatively there are UDF libraries (rfunc for example) that provide date format functions. So you could write something like: DateToStr(current_timestamp, '%d%m%Y%h%M%S') > Still using Firebird 1.5.3 (Hope to convert down the road) In that case, use DateToStr or CASE. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
RE: [firebird-support] How do I convert a timestamp to a string from within a stored procedure?
Extract(day,month ... From ?date) And then some ifs and etc Regards, Bogdan Lp, Bogdan Sent from Win 7.5 mobile -Original Message- From: SoftTech Sent: 26.1.2012 12:50 To: firebird-support@yahoogroups.com Subject: [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 [The entire original message is not included.] [Non-text portions of this message have been removed]
RE: [firebird-support] How do I convert a timestamp to a string from within a stored procedure?
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 MMDDHHMMSS 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 = '00';
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]
Re: {Disarmed} [firebird-support] How do I convert a timestamp to a string from within a stored procedure?
I think I have something figured out...
- 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]
Re: {Disarmed} [firebird-support] How do I convert a timestamp to a string from within a stored procedure?
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 = '00';
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]
