473,570 Members | 2,909 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Date format

I used the following query to retrieve the date in dd-mon-yyyy format.
db2 => SELECT RTRIM(CHAR(DAY( COVG_TYP_STRT_D T))) || '-' ||
RTRIM(MONTHNAME (COVG_TYP_STRT_ DT)) || '-' ||
RTRIM(CHAR(YEAR (COVG_TYP_STRT_ DT))) FROM twd_coverage_ty pe

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.

May 22 '06 #1
11 10555
Sandeep,

If I am not wrong, existing Timestamp_forma t 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

May 23 '06 #2
Mehmet Baserdem wrote:
Sandeep,

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


Indeed, you are not wrong. The TIMESTAMP_FORMA T 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_FORMA T 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(AFORMA T 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(ATIMEST AMP)
WHEN 'B' THEN MONTHNAME(ATIME STAMP)
WHEN 'c' THEN
CHAR(DATE(ATIME STAMP), LOCAL) || ' ' ||
CHAR(TIME(ATIME STAMP), LOCAL)
WHEN 'd' THEN SUBSTR(DIGITS(D AY(ATIMESTAMP)) , 9)
WHEN 'H' THEN SUBSTR(DIGITS(H OUR(ATIMESTAMP) ), 9)
WHEN 'I' THEN
SUBSTR(DIGITS(C ASE
WHEN HOUR(ATIMESTAMP ) = 0 THEN 12
WHEN HOUR(ATIMESTAMP ) > 12 THEN HOUR(ATIMESTAMP ) - 12
ELSE HOUR(ATIMESTAMP )
END), 9)
WHEN 'j' THEN SUBSTR(DIGITS(D AYOFYEAR(ATIMES TAMP)), 8)
WHEN 'm' THEN SUBSTR(DIGITS(M ONTH(ATIMESTAMP )), 9)
WHEN 'M' THEN SUBSTR(DIGITS(M INUTE(ATIMESTAM P)), 9)
WHEN 'P' THEN CASE WHEN HOUR(ATIMESTAMP ) < 12 THEN 'AM' ELSE
'PM' END
WHEN 'S' THEN SUBSTR(DIGITS(S ECOND(ATIMESTAM P)), 9)
WHEN 'U' THEN SUBSTR(DIGITS(W EEK(ATIMESTAMP) ), 9)
WHEN 'w' THEN CHAR(DAYOFWEEK( ATIMESTAMP))
WHEN 'W' THEN SUBSTR(DIGITS(W EEK_ISO(ATIMEST AMP)), 9)
WHEN 'x' THEN CHAR(DATE(ATIME STAMP), LOCAL)
WHEN 'X' THEN CHAR(TIME(ATIME STAMP), LOCAL)
WHEN 'y' THEN SUBSTR(DIGITS(Y EAR(ATIMESTAMP) ), 9)
WHEN 'Y' THEN SUBSTR(DIGITS(Y EAR(ATIMESTAMP) ), 7)
WHEN 'Z' THEN
CASE WHEN CURRENT TIMEZONE < 0 THEN '-' ELSE '+' END ||
SUBSTR(DIGITS(C URRENT TIMEZONE), 1, 2) || ':' ||
SUBSTR(DIGITS(C URRENT 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(AFORMA T VARCHAR(100), ADATE DATE)
RETURNS VARCHAR(100)
SPECIFIC STRFTIME2
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
STRFTIME(AFORMA T, TIMESTAMP(ADATE , '00:00:00'))@

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

CREATE FUNCTION STRFTIME(AFORMA T VARCHAR(100))
RETURNS VARCHAR(100)
SPECIFIC STRFTIME4
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
STRFTIME(AFORMA T, 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.

--

May 23 '06 #3
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?

May 24 '06 #4
Here is another trial for the function. I used %p for AM or PM.

CREATE FUNCTION STRFTIME_N(AFOR MAT 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(ATIMEST AMP))
, '%B', MONTHNAME(ATIME STAMP))
, '%c', CHAR(DATE(ATIME STAMP), LOCAL) ||' '||
CHAR(TIME(ATIME STAMP), LOCAL))
, '%d', SUBSTR(DIGITS(D AY(ATIMESTAMP)) ,9,2))
, '%H', SUBSTR(DIGITS(H OUR(ATIMESTAMP) ),9,2))
, '%I', SUBSTR(DIGITS(M OD(HOUR(ATIMEST AMP)+11,12)+1), 9,2))
, '%j', SUBSTR(DIGITS(D AYOFYEAR(ATIMES TAMP)),8,3))
, '%m', SUBSTR(DIGITS(M ONTH(ATIMESTAMP )),9,2))
, '%M', SUBSTR(DIGITS(M INUTE(ATIMESTAM P)),9,2))
, '%p', SUBSTR('AMPM',1 +HOUR(ATIMESTAM P)/12*2,2))
, '%S', SUBSTR(DIGITS(S ECOND(ATIMESTAM P)),9,2))
, '%U', SUBSTR(DIGITS(W EEK(ATIMESTAMP) ),9,2))
, '%w', SUBSTR(CHAR(DAY OFWEEK(ATIMESTA MP)),1,1))
, '%W', SUBSTR(DIGITS(W EEK_ISO(ATIMEST AMP)),9,2))
, '%x', CHAR(DATE(ATIME STAMP),LOCAL))
, '%X', CHAR(TIME(ATIME STAMP),LOCAL))
, '%y', SUBSTR(DIGITS(Y EAR(ATIMESTAMP) ),9,2))
, '%Y', SUBSTR(DIGITS(Y EAR(ATIMESTAMP) ),7,4))
, '%Z', SUBSTR('-++',2+INT(SIGN( CURRENT_TIMEZON E)),1)
||
TRANSLATE('AB:C D',DIGITS(CURRE NT_TIMEZONE),'A BCDEF'))
, '%%', '%');

May 24 '06 #5
Tonkuma wrote:
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?


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(AFORMA T 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(AT IMESTAMP), 3)
WHEN 'A' THEN DAYNAME(ATIMEST AMP)
WHEN 'b' THEN LEFT(MONTHNAME( ATIMESTAMP), 3)
WHEN 'B' THEN MONTHNAME(ATIME STAMP)
WHEN 'c' THEN
CHAR(DATE(ATIME STAMP), LOCAL) || ' ' ||
CHAR(TIME(ATIME STAMP), LOCAL)
WHEN 'd' THEN RIGHT(DIGITS(DA Y(ATIMESTAMP)), 2)
WHEN 'h' THEN CASE WHEN MONTH(ATIMESTAM P) < 6 THEN '1' ELSE
'2' END
WHEN 'H' THEN RIGHT(DIGITS(HO UR(ATIMESTAMP)) , 2)
WHEN 'I' THEN
RIGHT(DIGITS(CA SE
WHEN HOUR(ATIMESTAMP ) = 0 THEN 12
WHEN HOUR(ATIMESTAMP ) > 12 THEN HOUR(ATIMESTAMP ) - 12
ELSE HOUR(ATIMESTAMP )
END), 2)
WHEN 'j' THEN RIGHT(DIGITS(DA YOFYEAR(ATIMEST AMP)), 3)
WHEN 'm' THEN RIGHT(DIGITS(MO NTH(ATIMESTAMP) ), 2)
WHEN 'M' THEN RIGHT(DIGITS(MI NUTE(ATIMESTAMP )), 2)
WHEN 'p' THEN CASE WHEN HOUR(ATIMESTAMP ) < 12 THEN 'AM' ELSE
'PM' END
WHEN 'q' THEN LEFT(CHAR(QUART ER(ATIMESTAMP)) , 1)
WHEN 'S' THEN RIGHT(DIGITS(SE COND(ATIMESTAMP )), 2)
WHEN 'U' THEN RIGHT(DIGITS(WE EK(ATIMESTAMP)) , 2)
WHEN 'w' THEN LEFT(CHAR(DAYOF WEEK(ATIMESTAMP )), 1)
WHEN 'W' THEN RIGHT(DIGITS(WE EK_ISO(ATIMESTA MP)), 2)
WHEN 'x' THEN CHAR(DATE(ATIME STAMP), LOCAL)
WHEN 'X' THEN CHAR(TIME(ATIME STAMP), LOCAL)
WHEN 'y' THEN RIGHT(DIGITS(YE AR(ATIMESTAMP)) , 2)
WHEN 'Y' THEN RIGHT(DIGITS(YE AR(ATIMESTAMP)) , 4)
WHEN 'Z' THEN
CASE WHEN CURRENT TIMEZONE < 0 THEN '-' ELSE '+' END ||
SUBSTR(DIGITS(C URRENT TIMEZONE), 1, 2) || ':' ||
SUBSTR(DIGITS(C URRENT 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(AFORMA T VARCHAR(100), ADATE DATE)
RETURNS VARCHAR(100)
SPECIFIC STRFTIME2
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
STRFTIME(AFORMA T, TIMESTAMP(ADATE , '00:00:00'))@

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

CREATE FUNCTION STRFTIME(AFORMA T VARCHAR(100))
RETURNS VARCHAR(100)
SPECIFIC STRFTIME4
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
STRFTIME(AFORMA T, 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.

--

May 24 '06 #6
Tonkuma wrote:
Here is another trial for the function. I used %p for AM or PM.

CREATE FUNCTION STRFTIME_N(AFOR MAT VARCHAR(100), ATIMESTAMP TIMESTAMP)
RETURNS VARCHAR(100)
SPECIFIC STRFTIME_N
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
REPLACE(
REPLACE( [...snipped lots of REPLACE calls...]
)),7,4)) , '%Z', SUBSTR('-++',2+INT(SIGN( CURRENT_TIMEZON E)),1)
||
TRANSLATE('AB:C D',DIGITS(CURRE NT_TIMEZONE),'A BCDEF'))
, '%%', '%');


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.

--

May 24 '06 #7
> 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'.


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(ATIMEST AMP) and '%B', MONTHNAME(ATIME STAMP) before them.(In
English)

DROP FUNCTION STRFTIME_N;

CREATE FUNCTION STRFTIME_N(AFOR MAT 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(M INUTE(ATIMESTAM P)),9,2))
, '%W', SUBSTR(DIGITS(W EEK_ISO(ATIMEST AMP)),9,2))
, '%S', SUBSTR(DIGITS(S ECOND(ATIMESTAM P)),9,2))
, '%A', DAYNAME(ATIMEST AMP))
, '%B', MONTHNAME(ATIME STAMP))
, '%c', CHAR(DATE(ATIME STAMP),LOCAL) ||' '||
CHAR(TIME(ATIME STAMP),LOCAL))
, '%d', SUBSTR(DIGITS(D AY(ATIMESTAMP)) ,9,2))
, '%h', SUBSTR(CHAR(QUA RTER(ATIMESTAMP )/2),1,1))
, '%H', SUBSTR(DIGITS(H OUR(ATIMESTAMP) ),9,2))
, '%I', SUBSTR(DIGITS(M OD(HOUR(ATIMEST AMP)+11,12)+1), 9,2))
, '%j', SUBSTR(DIGITS(D AYOFYEAR(ATIMES TAMP)),8,3))
, '%m', SUBSTR(DIGITS(M ONTH(ATIMESTAMP )),9,2))
, '%p', SUBSTR('AMPM',1 +HOUR(ATIMESTAM P)/12*2,2))
, '%q', SUBSTR(CHAR(QUA RTER(ATIMESTAMP )),1,1))
, '%U', SUBSTR(DIGITS(W EEK(ATIMESTAMP) ),9,2))
, '%w', SUBSTR(CHAR(DAY OFWEEK(ATIMESTA MP)),1,1))
, '%x', CHAR(DATE(ATIME STAMP),LOCAL))
, '%X', CHAR(TIME(ATIME STAMP),LOCAL))
, '%y', SUBSTR(DIGITS(Y EAR(ATIMESTAMP) ),9,2))
, '%Y', SUBSTR(DIGITS(Y EAR(ATIMESTAMP) ),7,4))
, '%Z', SUBSTR('-++',2+INT(SIGN( CURRENT_TIMEZON E)),1)
||
TRANSLATE('AB:C D',DIGITS(CURRE NT_TIMEZONE),'A BCDEF'))
, 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

May 25 '06 #8
Tonkuma wrote:
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'.
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(ATIMEST AMP) and '%B', MONTHNAME(ATIME STAMP) before
them.(In English)


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:
DROP FUNCTION STRFTIME_N;

CREATE FUNCTION STRFTIME_N(AFOR MAT 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(M INUTE(ATIMESTAM P)),9,2))
, '%W', SUBSTR(DIGITS(W EEK_ISO(ATIMEST AMP)),9,2))
, '%S', SUBSTR(DIGITS(S ECOND(ATIMESTAM P)),9,2))
, '%A', DAYNAME(ATIMEST AMP))
, '%B', MONTHNAME(ATIME STAMP))
, '%c', CHAR(DATE(ATIME STAMP),LOCAL) ||' '||
CHAR(TIME(ATIME STAMP),LOCAL))
, '%d', SUBSTR(DIGITS(D AY(ATIMESTAMP)) ,9,2))
, '%h', SUBSTR(CHAR(QUA RTER(ATIMESTAMP )/2),1,1))
, '%H', SUBSTR(DIGITS(H OUR(ATIMESTAMP) ),9,2))
, '%I', SUBSTR(DIGITS(M OD(HOUR(ATIMEST AMP)+11,12)+1), 9,2))
, '%j', SUBSTR(DIGITS(D AYOFYEAR(ATIMES TAMP)),8,3))
, '%m', SUBSTR(DIGITS(M ONTH(ATIMESTAMP )),9,2))
, '%p', SUBSTR('AMPM',1 +HOUR(ATIMESTAM P)/12*2,2))
That's a lot nicer than my messy CASE expression for the AM/PM case
there.
, '%q', SUBSTR(CHAR(QUA RTER(ATIMESTAMP )),1,1))
, '%U', SUBSTR(DIGITS(W EEK(ATIMESTAMP) ),9,2))
, '%w', SUBSTR(CHAR(DAY OFWEEK(ATIMESTA MP)),1,1))
, '%x', CHAR(DATE(ATIME STAMP),LOCAL))
, '%X', CHAR(TIME(ATIME STAMP),LOCAL))
, '%y', SUBSTR(DIGITS(Y EAR(ATIMESTAMP) ),9,2))
, '%Y', SUBSTR(DIGITS(Y EAR(ATIMESTAMP) ),7,4))
, '%Z', SUBSTR('-++',2+INT(SIGN( CURRENT_TIMEZON E)),1)
||
TRANSLATE('AB:C D',DIGITS(CURRE NT_TIMEZONE),'A BCDEF'))
, CHR(1), '%');
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.
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


Thanks,

Dave.

--

May 25 '06 #9
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_ty p

Thanks a lot...
RaInDeEr

May 25 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3380
by: Alistair | last post by:
it's the idiot with his query strings again...."yippeee" I hear aaron shout this time the problem is with a date format query I have a query string thus strSQL = "SELECT * FROM users where creation_date >= #" & strthisweekstart & "# AND creation_date <= #" & strthisweekend & "#" strthisweek start is dateadd("d",-7,date) and...
2
2666
by: Ian | last post by:
I have a problem I hope someone can point out where I am going wrong. I need to store a date/time in a cookie ( ie the date a visitor last visited ) However the date format changes to US, despite LCID being set to UK. I have googled extensively but can't work it out. This is my code to give write the current date/time to screen: <%...
1
60893
by: jt | last post by:
I posted this yesterday, but I am not seeing this out yet: I am having problems with updating a date field in a certain format. The data is stored in an Oracle database. The date is automatically displayed in format, "mm/dd/yyyy" on the ASP page, but it is accepted only in another format, "dd/mon/yy". I want to make it consistent. How can...
15
42986
by: Simon Brooke | last post by:
I'm investigating a bug a customer has reported in our database abstraction layer, and it's making me very unhappy. Brief summary: I have a database abstraction layer which is intended to mediate between webapps and arbitrary database backends using JDBC. I am very unwilling indeed to write special-case code for particular databases. Our...
6
31134
by: Dario Di Bella | last post by:
Hi all, we have the following urgent issue affecting our development team. Initially we had one particular workstation that failed executing queries on a DB2 database, raising an invalid date format exception (SQLSTATE=22007). The same queries worked fine on all the other workstations. The date format we want to use is "dd/mm/yyyy".After...
5
816
by: Macca | last post by:
Hi, I have a table which has a date/time field. I am storing them as follows :- 01/01/2005 11:25 01/01/2005 19:44 02/01/2005 05:04
20
35432
by: andreas | last post by:
When I copy a vb.net project using date formats from one PC with a windows date format f.e. dd/mm/yyyy to another PC having a format yy/mm/dd then I get errors. How can I change for a while in the project the date format in vb.code ( not in Windows) and how can I find out which date format the PC Windows is using. Thanks for any response
2
16771
by: thewilldog | last post by:
Hello, I've reviewed the archives here to address the issue, but I'm still running into problems. I've got a table field populated with the record date in text "YYYYMMDD" To convert it into a recognizable date format, I've done the following: Query 1: References Source Table; Isolates Year, Day; creates MMDD field Acc Open Year: Left(,4)...
10
5796
by: ARC | last post by:
Hello all, General question for back-end database that has numerous date fields where the database will be used in regions that put the month first, and regions that do not. Should I save a date format in the table design, such as: mm/dd/yyyy? What I've done for years is to store the date format in date fields, then on the forms, based...
3
421
by: Tony WONG | last post by:
The date format (control panel) of testing (VS2005) and production platform are yyyy/mm/dd. i run preview from testing (VS2005). The date format is yyyy/mm/dd however, when i put the code to production platform. The date format is dd/mm/yyyy what should i look to? Thanks a lot.
0
7729
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7637
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8000
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6332
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5523
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5247
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3671
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
974
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.