473,839 Members | 1,352 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 10581
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
3395
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 strthisweekend is date(). The
2
2683
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: <% Session.LCID = 2057 Response.Write Now() & "<br>" &
1
60939
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 I force it to accept the date in format "mm/dd/yyyy"? The second option would be to force it to...
15
43021
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 code has worked satisfactorily with many databases, including many instances MS SQLServer 2000...
6
31192
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 reinstalling several times the db2 runtime client w/ different options, we found on a technical forum...
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
35662
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
16817
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) Acc Open Day: Right(,2) MMDD: Right(,4) Query 2: References Query 1; Isolates Month
10
5830
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 on their region, I would set the date formats on form_load
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
9855
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10908
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10587
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10649
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10295
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9426
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7829
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
2
4064
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3136
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.