Getting English Day Name

[singlal]

Hi,
I am currently using DAYOFWEEK function and putting CASE statement to
get english day name. For example:

SELECT
CASE DAYOFWEEK(CURRENT DATE)
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUEDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
FROM SYSIBM.SYSDUMMY1

The function DAYNAME is not working (or I am not using it rightly). Is
there any other function that can be used to get the english day name
as string? If DAYNAME can be used, what should be its parameter?

Thanks in advance,
Lalit

[Jan M. Nelken]

singlal wrote:

The function DAYNAME is not working (or I am not using it rightly). Is
there any other function that can be used to get the english day name
as string? If DAYNAME can be used, what should be its parameter?

Which version of DB2 you are using?

I don't believe you tried this function at all; just a simple test would give
you correct answer:

D:\Working>db2 values dayname(current date)

1
---------------------------------------------------------------------------
Wednesday

D:\Working>db2 values dayname(current timestamp)

1
---------------------------------------------------------------------------
Wednesday
1 record(s) selected.
Or a quick search for product documentation (available online at:
http://publib.boulder.ibm.com/infoce.../v8//index.jsp)
would reveal that:

DAYNAME scalar function

-DAYNAME--(--expression--)-----------------------------------><

The schema is SYSFUN.

Returns a mixed case character string containing the name of the day (for
example, Friday) for the day portion of the argument based on the locale when
the database was started.

The argument must be a date, timestamp, or a valid character string
representation of a date or timestamp that is neither a CLOB nor a LONG VARCHAR.
In a Unicode database, if a supplied argument is a graphic string, it is first
converted to a character string before the function is executed.

The result of the function is VARCHAR(100). The result can be null; if the
argument is null, the result is the null value.
Jan M. Nelken

[Knut Stolze]

Jan M. Nelken wrote:

singlal wrote:

The function DAYNAME is not working (or I am not using it rightly). Is
there any other function that can be used to get the english day name
as string? If DAYNAME can be used, what should be its parameter?

Which version of DB2 you are using?

Good question given that the DAYNAME function already existed in V7 and
probably even much earlier.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[singlal]

I am working with DB2 v 7.1. I am not sure why but DAYNAME is not
working. My query is SELECT DAYNAME(CURRENT TIMESTAMP). Error I am
getting is SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME DAYNAME
HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH.

[Knut Stolze]

singlal wrote:

I am working with DB2 v 7.1. I am not sure why but DAYNAME is not
working. My query is SELECT DAYNAME(CURRENT TIMESTAMP). Error I am
getting is SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME DAYNAME
HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH.

So what's the current function path?
Have you changed your operating system's clock at some time?
Can't you move to V8, given that V7 is out of service for over a year (and
V7.1 even longer)?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[singlal]

Moving to V8 is out of my hand. It's an org wide decision that's
pending for a while now.

What do you mean by current function path? How do I find it? The
functions like DAYOFWEEK are working fine.

[richard_mccutcheon]

Here is the blurb on the function path. It is from the v7 infocentre:

Function Path
The concept of function path is central to DB2's resolution of
unqualified references that occur when you do not use the schema-name.
For the use of function path in DDL statements that refer to functions,
refer to the SQL Reference. The function path is an ordered list of
schema names. It provides a set of schemas for resolving unqualified
function references to UDFs and methods as well as UDTs. In cases where
a function reference matches functions in more than one schema in the
path, the order of the schemas in the path is used to resolve this
match. The function path is established by means of the FUNCPATH option
on the precompile and bind commands for static SQL. The function path
is set by the SET CURRENT FUNCTION PATH statement for dynamic SQL. The
function path has the following default value:

"SYSIBM","SYSFUN","<ID>"

This applies to both static and dynamic SQL, where <ID> represents the
current statement authorization ID.

Also, perhaps you can try prefacing the function name with "sysfun.".
If that works you know you have a function path issue.
Regards,
Richard McCutcheon.

[singlal]

Still getting -440 SQLCODE. SELECT SYSFUN.DAYNAME(CURRENT DATE) FROM
SYSIBM.SYSDUMMY1.

My confusion is more because DAYOFWEEK is working without having to
qualify. This function also has schema SYSFUN like DAYNAME.

[richard_mccutcheon]

Have you tried your SQL while qualifying the function with a schema?

ex/ db2 "select sysfun.dayname(current date) from sysibm.sysdummy1"
Also, here is the blurb on function path from the v7 info centre.

Function Path
The concept of function path is central to DB2's resolution of
unqualified references that occur when you do not use the schema-name.
For the use of function path in DDL statements that refer to functions,
refer to the SQL Reference. The function path is an ordered list of
schema names. It provides a set of schemas for resolving unqualified
function references to UDFs and methods as well as UDTs. In cases where
a function reference matches functions in more than one schema in the
path, the order of the schemas in the path is used to resolve this
match. The function path is established by means of the FUNCPATH option
on the precompile and bind commands for static SQL. The function path
is set by the SET CURRENT FUNCTION PATH statement for dynamic SQL. The
function path has the following default value:

"SYSIBM","SYSFUN","<ID>"

This applies to both static and dynamic SQL, where <ID> represents the
current statement authorization ID.
Hope this helps point you in the right direction.

Regards,
Richard McCutcheon.

[Knut Stolze]

singlal wrote:

Still getting -440 SQLCODE. SELECT SYSFUN.DAYNAME(CURRENT DATE) FROM
SYSIBM.SYSDUMMY1.

My confusion is more because DAYOFWEEK is working without having to
qualify. This function also has schema SYSFUN like DAYNAME.

Then have a look at the SYSCAT.FUNCTIONS catalog view to see if there is a
DAYNAME function listed. If it isn't, I'd suspect that either (a) the V7
manual is not correct about the existence of the function, or (b) something
is garbled in your database.

And if the function exists in the catalog, check its parameters (I believe
SYSCAT.FUNCTIONPARMS or something like that). It should have two
parameters: one input (ordinal = 1) and one output (ordinal = 0) with the
data types DATE and VARCHAR, respectively. If you find all the catalog
entries, then something in your system is not as it should be, which leads
me back to the question if someone changed the system clock on the machine.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Ian]

singlal wrote:

I am working with DB2 v 7.1. I am not sure why but DAYNAME is not
working. My query is SELECT DAYNAME(CURRENT TIMESTAMP). Error I am
getting is SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME DAYNAME
HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH.

Are you on z/OS or Linux/UNIX/Windows? [I ask because I've never
seen distributed messages in ALL CAPS, they're usually in mixed case].

[Tonkuma]

I think Ian is right.
DB2 for z/OS desn't support DAYNAME even newest Version8.
But, UDF written in C/C++ were supplied by DB2. See SQL Reference
Appendix H. Sample user-defined functions.