I used the following query to retrieve the date in dd-mon-yyyy format.
db2 => SELECT RTRIM(CHAR(DAY(COVG_TYP_STRT_DT))) || '-' ||
RTRIM(MONTHNAME(COVG_TYP_STRT_DT)) || '-' ||
RTRIM(CHAR(YEAR(COVG_TYP_STRT_DT))) FROM twd_coverage_type

1
----------------------------------------------------------------------------------------------------------------------------
6-May-2006

1 record(s) selected.

Is there any shorter way to do this?
example:
SELECT CHAR(T1.COL_DT ,'DD-MMM-YYYY') FROM TABLE1 T1;
This works fine in oracle pl/sql but not in db2.
My db2 version is 8.2 and aix v5.3

Cheers,
San.

Sandeep,

If I am not wrong, existing Timestamp_format function is not helping
you.

try something like this

CREATE FUNCTION TO_DD_MMM_YYYY (D DATE)
RETURNS VARCHAR(11)
LANGUAGE SQL
BEGIN ATOMIC
return cast (day(d) as char(2)) || '-' || cast ( MONTHNAME(D) as
char(3)) || '-' || cast ( YEAR(D) as char(4));
end@

regards,

Mehmet Baserdem

Mehmet Baserdem wrote:

Indeed, you are not wrong. The TIMESTAMP_FORMAT function is comically
ridiculous. According to the documentation:

Valid format strings are:

'YYYY-MM-DD HH24:MI:SS'

When I first read this, I thought "oh, it's a typo, they must mean 'A
valid format string...'". But no, they really mean "the *only* valid
format string..."! Which had me scratching my head wondering, if you
can only specify that particular value for the second argument, why
bother having the second argument?

Anyway, here's a fun little (!) function which is a bit more flexible.
It's probably horribly inefficient compared to a proper C
implementation, but at least it allows you to specify more than one
format :-)

The function is based off the strftime() function and accepts the
following substitution templates in the AFORMAT parameter:

Template Meaning
======== ==================================================
%A Locale's full weekday name.
%B Locale's full month name.
%c Locale's appropriate date and time representation.
%d Day of the month as a decimal number [01,31].
%H Hour (24-hour clock) as a decimal number [00,23].
%I Hour (12-hour clock) as a decimal number [01,12].
%j Day of the year as a decimal number [001,366].
%m Month as a decimal number [01,12].
%M Minute as a decimal number [00,59].
%p Locale's equivalent of either AM or PM.
%S Second as a decimal number [00,61].
%U Week number of the year (Sunday as the first day of the
week) as a decimal number [01,54].
%w Weekday as a decimal number [1(Sunday),7].
%W Week number of the year (Monday as the first day of the
week) as a decimal number [01,53].
%x Locale's appropriate date representation.
%X Locale's appropriate time representation.
%y Year without century as a decimal number [00,99].
%Y Year with century as a decimal number.
%Z Time zone offset (no characters if no time zone exists).
%% A literal "%" character.

Note this isn't exactly the same as the "normal" strftime() function as
things like abbreviated day / month name are missing. That said, it's
not difficult to expand the function if you want to add these things.

Also note that, unlike the TIMESTAMP_FORMAT function the format
parameter is specified first (I did it this way round because that's
the order of the parameters in the original strftime() function, but of
course you can switch them back 'round if you want).

Overloaded versions that accept DATE and TIME parameters are also
provided, as is a version which takes only the format as a parameter
and uses the CURRENT TIMESTAMP special register for the timestamp to be
formatted:

CREATE FUNCTION STRFTIME(AFORMAT VARCHAR(100), ATIMESTAMP TIMESTAMP)
RETURNS VARCHAR(100)
SPECIFIC STRFTIME1
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN ATOMIC
DECLARE I SMALLINT;
DECLARE C CHAR(1);
DECLARE RESULT VARCHAR(100);
SET I = 1;
SET RESULT = '';
WHILE I <= LENGTH(AFORMAT) DO
SET C = SUBSTR(AFORMAT, I, 1);
IF C = '%' AND I < LENGTH(AFORMAT) THEN
SET RESULT = RESULT ||
CASE SUBSTR(AFORMAT, I + 1, 1)
WHEN '%' THEN '%'
WHEN 'A' THEN DAYNAME(ATIMESTAMP)
WHEN 'B' THEN MONTHNAME(ATIMESTAMP)
WHEN 'c' THEN
CHAR(DATE(ATIMESTAMP), LOCAL) || ' ' ||
CHAR(TIME(ATIMESTAMP), LOCAL)
WHEN 'd' THEN SUBSTR(DIGITS(DAY(ATIMESTAMP)), 9)
WHEN 'H' THEN SUBSTR(DIGITS(HOUR(ATIMESTAMP)), 9)
WHEN 'I' THEN
SUBSTR(DIGITS(CASE
WHEN HOUR(ATIMESTAMP) = 0 THEN 12
WHEN HOUR(ATIMESTAMP) > 12 THEN HOUR(ATIMESTAMP) - 12
ELSE HOUR(ATIMESTAMP)
END), 9)
WHEN 'j' THEN SUBSTR(DIGITS(DAYOFYEAR(ATIMESTAMP)), 8)
WHEN 'm' THEN SUBSTR(DIGITS(MONTH(ATIMESTAMP)), 9)
WHEN 'M' THEN SUBSTR(DIGITS(MINUTE(ATIMESTAMP)), 9)
WHEN 'P' THEN CASE WHEN HOUR(ATIMESTAMP) < 12 THEN 'AM' ELSE
'PM' END
WHEN 'S' THEN SUBSTR(DIGITS(SECOND(ATIMESTAMP)), 9)
WHEN 'U' THEN SUBSTR(DIGITS(WEEK(ATIMESTAMP)), 9)
WHEN 'w' THEN CHAR(DAYOFWEEK(ATIMESTAMP))
WHEN 'W' THEN SUBSTR(DIGITS(WEEK_ISO(ATIMESTAMP)), 9)
WHEN 'x' THEN CHAR(DATE(ATIMESTAMP), LOCAL)
WHEN 'X' THEN CHAR(TIME(ATIMESTAMP), LOCAL)
WHEN 'y' THEN SUBSTR(DIGITS(YEAR(ATIMESTAMP)), 9)
WHEN 'Y' THEN SUBSTR(DIGITS(YEAR(ATIMESTAMP)), 7)
WHEN 'Z' THEN
CASE WHEN CURRENT TIMEZONE < 0 THEN '-' ELSE '+' END ||
SUBSTR(DIGITS(CURRENT TIMEZONE), 1, 2) || ':' ||
SUBSTR(DIGITS(CURRENT TIMEZONE), 3, 2)
ELSE ''
END;
SET I = I + 2;
ELSE
SET RESULT = RESULT || C;
SET I = I + 1;
END IF;
END WHILE;
RETURN RESULT;
END@

CREATE FUNCTION STRFTIME(AFORMAT VARCHAR(100), ADATE DATE)
RETURNS VARCHAR(100)
SPECIFIC STRFTIME2
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
STRFTIME(AFORMAT, TIMESTAMP(ADATE, '00:00:00'))@

CREATE FUNCTION STRFTIME(AFORMAT VARCHAR(100), ATIME TIME)
RETURNS VARCHAR(100)
SPECIFIC STRFTIME3
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
STRFTIME(AFORMAT, TIMESTAMP('0001-01-01', ATIME))@

CREATE FUNCTION STRFTIME(AFORMAT VARCHAR(100))
RETURNS VARCHAR(100)
SPECIFIC STRFTIME4
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
STRFTIME(AFORMAT, CURRENT TIMESTAMP)@

Some examples of usage:

STRFTIME('%A, %d %B %Y') = 'Tuesday, 23 May 2006'
STRFTIME('%Y-%m-%dT%H:%M:%S%Z') = '2006-05-23T23:47:10+01:00'
STRFTIME('Day: %d') = 'Day: 23'
STRFTIME('%Y-%j (DOY)') = '2006-143 (DOY)'
STRFTIME('%I:%M:%S %P') = '11:58:57 PM'


If you find any bugs or need a hand extending it to handle other
things, let me know.

HTH,

Dave.

--

You wrote "%p Locale's equivalent of either AM or PM." in
"Template Meaning".
But you coded it as %P and in "Some examples of usage:" you showed
STRFTIME('%I:%M:%S %P') = '11:58:57 PM'
Which is right?

Here is another trial for the function. I used %p for AM or PM.

CREATE FUNCTION STRFTIME_N(AFORMAT VARCHAR(100), ATIMESTAMP TIMESTAMP)
RETURNS VARCHAR(100)
SPECIFIC STRFTIME_N
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(AFORMAT
, '%A', DAYNAME(ATIMESTAMP))
, '%B', MONTHNAME(ATIMESTAMP))
, '%c', CHAR(DATE(ATIMESTAMP), LOCAL) ||' '||
CHAR(TIME(ATIMESTAMP), LOCAL))
, '%d', SUBSTR(DIGITS(DAY(ATIMESTAMP)),9,2))
, '%H', SUBSTR(DIGITS(HOUR(ATIMESTAMP)),9,2))
, '%I', SUBSTR(DIGITS(MOD(HOUR(ATIMESTAMP)+11,12)+1),9,2))
, '%j', SUBSTR(DIGITS(DAYOFYEAR(ATIMESTAMP)),8,3))
, '%m', SUBSTR(DIGITS(MONTH(ATIMESTAMP)),9,2))
, '%M', SUBSTR(DIGITS(MINUTE(ATIMESTAMP)),9,2))
, '%p', SUBSTR('AMPM',1+HOUR(ATIMESTAMP)/12*2,2))
, '%S', SUBSTR(DIGITS(SECOND(ATIMESTAMP)),9,2))
, '%U', SUBSTR(DIGITS(WEEK(ATIMESTAMP)),9,2))
, '%w', SUBSTR(CHAR(DAYOFWEEK(ATIMESTAMP)),1,1))
, '%W', SUBSTR(DIGITS(WEEK_ISO(ATIMESTAMP)),9,2))
, '%x', CHAR(DATE(ATIMESTAMP),LOCAL))
, '%X', CHAR(TIME(ATIMESTAMP),LOCAL))
, '%y', SUBSTR(DIGITS(YEAR(ATIMESTAMP)),9,2))
, '%Y', SUBSTR(DIGITS(YEAR(ATIMESTAMP)),7,4))
, '%Z', SUBSTR('-++',2+INT(SIGN(CURRENT_TIMEZONE)),1)
, '%%', '%');

Tonkuma wrote:

D'oh! You're absolutely right. Going by the strftime man-page on my
Linux box, it ought to be lower-case 'p'. There's a few other changes
I've made and some tidying up (e.g. I replaced most of the SUBSTR()
calls with RIGHT() which is a bit clearer under the circumstances.

I've added the abbreviated versions of weekday and month name simply by
taking the first 3 characters of the output of DAYNAME and MONTHNAME.
Probably incorrect for anything but an English locale, but coming up
with anything more complex ... well ... I'm lazy :-)

I've also added some "non-standard" substitutions like '%q' for the
quarter number, and '%h' for half, so you can have templates like:

STRFTIME('%qQ%y') = '2Q06'

(which are some representations of approximate dates I've seen
occassionaly a fairly common representation of approximate dates in
business).

Here's the revised version:

DROP SPECIFIC FUNCTION STRFTIME4@
DROP SPECIFIC FUNCTION STRFTIME3@
DROP SPECIFIC FUNCTION STRFTIME2@
DROP SPECIFIC FUNCTION STRFTIME1@

CREATE FUNCTION STRFTIME(AFORMAT VARCHAR(100), ATIMESTAMP TIMESTAMP)
RETURNS VARCHAR(100)
SPECIFIC STRFTIME1
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN ATOMIC
DECLARE I SMALLINT;
DECLARE C CHAR(1);
DECLARE RESULT VARCHAR(100);
SET I = 1;
SET RESULT = '';
WHILE I <= LENGTH(AFORMAT) DO
SET C = SUBSTR(AFORMAT, I, 1);
IF C = '%' AND I < LENGTH(AFORMAT) THEN
SET RESULT = RESULT ||
CASE SUBSTR(AFORMAT, I + 1, 1)
WHEN '%' THEN '%'
WHEN 'a' THEN LEFT(DAYNAME(ATIMESTAMP), 3)
WHEN 'A' THEN DAYNAME(ATIMESTAMP)
WHEN 'b' THEN LEFT(MONTHNAME(ATIMESTAMP), 3)
WHEN 'B' THEN MONTHNAME(ATIMESTAMP)
WHEN 'c' THEN
CHAR(DATE(ATIMESTAMP), LOCAL) || ' ' ||
CHAR(TIME(ATIMESTAMP), LOCAL)
WHEN 'd' THEN RIGHT(DIGITS(DAY(ATIMESTAMP)), 2)
WHEN 'h' THEN CASE WHEN MONTH(ATIMESTAMP) < 6 THEN '1' ELSE
'2' END
WHEN 'H' THEN RIGHT(DIGITS(HOUR(ATIMESTAMP)), 2)
WHEN 'I' THEN
RIGHT(DIGITS(CASE
WHEN HOUR(ATIMESTAMP) = 0 THEN 12
WHEN HOUR(ATIMESTAMP) > 12 THEN HOUR(ATIMESTAMP) - 12
ELSE HOUR(ATIMESTAMP)
END), 2)
WHEN 'j' THEN RIGHT(DIGITS(DAYOFYEAR(ATIMESTAMP)), 3)
WHEN 'm' THEN RIGHT(DIGITS(MONTH(ATIMESTAMP)), 2)
WHEN 'M' THEN RIGHT(DIGITS(MINUTE(ATIMESTAMP)), 2)
WHEN 'p' THEN CASE WHEN HOUR(ATIMESTAMP) < 12 THEN 'AM' ELSE
'PM' END
WHEN 'q' THEN LEFT(CHAR(QUARTER(ATIMESTAMP)), 1)
WHEN 'S' THEN RIGHT(DIGITS(SECOND(ATIMESTAMP)), 2)
WHEN 'U' THEN RIGHT(DIGITS(WEEK(ATIMESTAMP)), 2)
WHEN 'w' THEN LEFT(CHAR(DAYOFWEEK(ATIMESTAMP)), 1)
WHEN 'W' THEN RIGHT(DIGITS(WEEK_ISO(ATIMESTAMP)), 2)
WHEN 'x' THEN CHAR(DATE(ATIMESTAMP), LOCAL)
WHEN 'X' THEN CHAR(TIME(ATIMESTAMP), LOCAL)
WHEN 'y' THEN RIGHT(DIGITS(YEAR(ATIMESTAMP)), 2)
WHEN 'Y' THEN RIGHT(DIGITS(YEAR(ATIMESTAMP)), 4)
WHEN 'Z' THEN
CASE WHEN CURRENT TIMEZONE < 0 THEN '-' ELSE '+' END ||
SUBSTR(DIGITS(CURRENT TIMEZONE), 1, 2) || ':' ||
SUBSTR(DIGITS(CURRENT TIMEZONE), 3, 2)
ELSE ''
END;
SET I = I + 2;
ELSE
SET RESULT = RESULT || C;
SET I = I + 1;
END IF;
END WHILE;
RETURN RESULT;
END@

CREATE FUNCTION STRFTIME(AFORMAT VARCHAR(100), ADATE DATE)
RETURNS VARCHAR(100)
SPECIFIC STRFTIME2
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
STRFTIME(AFORMAT, TIMESTAMP(ADATE, '00:00:00'))@

CREATE FUNCTION STRFTIME(AFORMAT VARCHAR(100), ATIME TIME)
RETURNS VARCHAR(100)
SPECIFIC STRFTIME3
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
STRFTIME(AFORMAT, TIMESTAMP('0001-01-01', ATIME))@

CREATE FUNCTION STRFTIME(AFORMAT VARCHAR(100))
RETURNS VARCHAR(100)
SPECIFIC STRFTIME4
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
STRFTIME(AFORMAT, CURRENT TIMESTAMP)@


And some more examples:

STRFTIME('%d-%a-%Y') = '24-Wed-2006'
STRFTIME('%qQ%y') = '2Q06'
STRFTIME('%hH%y') = '1H06'
STRFTIME('%YQ%q') = '2006Q2'

HTH,

Dave.

--

Tonkuma wrote:

)),7,4))
Ah, I had originally thought about using nested REPLACE calls, but
there's a problem with that approach. Depending on the order of
replacements escaped % symbols in the template (i.e. %%) can cause
problems. Consider the format:

'%%p'

Which ought to result in the string '%p'. In your version of the
function, %p get's substitution before %% so initially you wind up with
'%PM' or possibly '%AM'. Thankfully, the replacement for %A has already
been done by that point, but if the ordering had been different you
might have wound up with 'WednesdayM'.

Now, if the substitution for %% is done first, you initially wind up
with '%p' but then the substitution for %p comes along and transforms
that into AM or PM.

Basically, this function can't be *reliably* written using nested (or
serial) REPLACE calls. It's just the wrong algorithm for it, sorry.


Dave.

--

How about first change '%%' to a character which usually is not used(I
tried CHR(1)).
And, I changed REPLACE sequence.
Put first characters which there are possibility to be produced by %A',
DAYNAME(ATIMESTAMP) and '%B', MONTHNAME(ATIMESTAMP) before them.(In
English)

DROP FUNCTION STRFTIME_N;

CREATE FUNCTION STRFTIME_N(AFORMAT VARCHAR(100), ATIMESTAMP TIMESTAMP)
RETURNS VARCHAR(100)
SPECIFIC STRFTIME_N
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(AFORMAT, '%%', CHR(1))
, '%M', SUBSTR(DIGITS(MINUTE(ATIMESTAMP)),9,2))
, '%W', SUBSTR(DIGITS(WEEK_ISO(ATIMESTAMP)),9,2))
, '%S', SUBSTR(DIGITS(SECOND(ATIMESTAMP)),9,2))
, '%A', DAYNAME(ATIMESTAMP))
, '%B', MONTHNAME(ATIMESTAMP))
, '%c', CHAR(DATE(ATIMESTAMP),LOCAL) ||' '||
CHAR(TIME(ATIMESTAMP),LOCAL))
, '%d', SUBSTR(DIGITS(DAY(ATIMESTAMP)),9,2))
, '%h', SUBSTR(CHAR(QUARTER(ATIMESTAMP)/2),1,1))
, '%H', SUBSTR(DIGITS(HOUR(ATIMESTAMP)),9,2))
, '%I', SUBSTR(DIGITS(MOD(HOUR(ATIMESTAMP)+11,12)+1),9,2))
, '%j', SUBSTR(DIGITS(DAYOFYEAR(ATIMESTAMP)),8,3))
, '%m', SUBSTR(DIGITS(MONTH(ATIMESTAMP)),9,2))
, '%p', SUBSTR('AMPM',1+HOUR(ATIMESTAMP)/12*2,2))
, '%q', SUBSTR(CHAR(QUARTER(ATIMESTAMP)),1,1))
, '%U', SUBSTR(DIGITS(WEEK(ATIMESTAMP)),9,2))
, '%w', SUBSTR(CHAR(DAYOFWEEK(ATIMESTAMP)),1,1))
, '%x', CHAR(DATE(ATIMESTAMP),LOCAL))
, '%X', CHAR(TIME(ATIMESTAMP),LOCAL))
, '%y', SUBSTR(DIGITS(YEAR(ATIMESTAMP)),9,2))
, '%Y', SUBSTR(DIGITS(YEAR(ATIMESTAMP)),7,4))
, '%Z', SUBSTR('-++',2+INT(SIGN(CURRENT_TIMEZONE)),1)
, CHR(1), '%');

Some result examples:
'%I:%M:%S %p' => 11:48:08 AM
'%I:%M:%S %%p' => 11:48:17 %p
'%I:%M:%S %%%p' => 11:48:24 %AM
'%I:%M:%S %%%%p' => 11:48:35 %%p
'%I:%M:%S %%%%%p' => 11:48:49 %%AM
'%qQ%y' => 2Q06

Tonkuma wrote:

Yes, that'll work ... for now :-) Although this version of the function
is now correct, what happens if someone wishes to extend the behavior
of the function? Although it is possible to do so safely, it requires
careful consideration of the order of replacement. In other words, this
definition is less maintainable than the iterative version.

There's also the question of performance. I'm unsure if this version
performs better or worse than the iterative version:

The iterative version makes a single pass of the template string,
constructing the output by repeatedly concatenating (urgh). This pass
is done in SQL and therefore (I assume) is going to be considerably
slower than a single pass done by the REPLACE() function (in compiled
code).

This definition of the function makes 23 passes of the template string
(in compiled code, by calling REPLACE 23 times). Is this quicker or
slower than a single pass done in SQL (especially when taking all that
concatenation into account)? I'm not sure.

If one wants to get pedantic about performance, another thing to
consider would be when or if the replacement strings are calculated. I
suspect that all the replacement strings are calculated once and once
only in this version (at runtime the REPLACE function doesn't know in
advance whether a template occurs in the format string, hence each call
to REPLACE *must* occur, requiring that the parameters for each call
are evaluated).

In contrast I suspect the iterative version calculates replacement
strings as and when required (although I'm not sure about this - it
rather depends on how the big CASE statement in the code is
interpreted).

Therefore this version would probably have a performance advantage when
a substitution template occurs multiple times in the format string (the
substituted value would only be calculated once), while the iterative
version would perform better if a small number of templates occurred
once within the format string.

In conclusion, I suspect typical use-cases of this function fit the
optimal case of the iterative version better than this version, but one
would have to profile both functions to find out which actually
performs better in practice.

For me, the maintainability is more important than the performance, so
I'll be sticking with the iterative version for now. That said, I like
some of the modifications you've made:

That's a lot nicer than my messy CASE expression for the AM/PM case
there.

Argh! I keep forgetting TRANSLATE can be used for things like this -
much better than my horrible code on the TIMEZONE replacement, and
that's an interesting way of getting the sign prefixed as well.

Thanks,

Dave.

--

hey Sandeep,

I needed a query with dates separated by '/ '. I took the sample from
your SQL..

SELECT RTRIM(CHAR(DAY(lst_updt_tms))) || '/' ||
RTRIM(MONTHNAME(lst_updt_tms)) || '/' ||
RTRIM(CHAR(YEAR(lst_updt_tms))) FROM db2nci.t_rpt_typ

Thanks a lot...
RaInDeEr