Regarding DAYOFWEEK function

[pankaj_wolfhunter]

Greetings,
In DB2, we have dayofweek function that returns a
numeric value for the current day (1 for sunday till 7 for saturday)

Is it possible to alter this function value, i mean 1 for monday till 7
for sunday?

Any help would be appreciated.

TIA

[Serge Rielau]

pa***************@yahoo.co.in wrote:

Greetings,
In DB2, we have dayofweek function that returns a
numeric value for the current day (1 for sunday till 7 for saturday)

Is it possible to alter this function value, i mean 1 for monday till 7
for sunday?

dayofweek_iso() :-)

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[pankaj_wolfhunter]

That was easy :-). How can we set at the instance level. i mean how is
the exact implementation.

[Serge Rielau]

pa***************@yahoo.co.in wrote:

That was easy :-). How can we set at the instance level. i mean how is
the exact implementation.

What's so bad about typing _ISO?
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[pankaj_wolfhunter]

I am not getting. i mean how can i set it through CLP?

[Serge Rielau]

pa***************@yahoo.co.in wrote:

I am not getting. i mean how can i set it through CLP?

dayofweek_iso() is a function.
Try this:
VALUES dayofweek_iso(current date)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[pankaj_wolfhunter]

Thanks for reply Serge, but I think i was not able to put my question
properly. If i do VALUES dayofweek_iso(current date), i get the numeric
value for the current day.
What i want is , some way through which i can set today(say tue) as the
first day of the week and mon as the last day at an instance level?

Is there a way?

TIA

[Ian]

pa***************@yahoo.co.in wrote:

Thanks for reply Serge, but I think i was not able to put my question
properly. If i do VALUES dayofweek_iso(current date), i get the numeric
value for the current day.
What i want is , some way through which i can set today(say tue) as the
first day of the week and mon as the last day at an instance level?

Is there a way?

You can't do this at the instance level.

[pankaj_wolfhunter]

Thanks Ian. Can we do it at database level?

[Knut Stolze]

pa***************@yahoo.co.in wrote:

Thanks Ian. Can we do it at database level?

No.

But you could always call the _ISO variation of the function and then decide
in your application which way to go. After all, you will usually convert
the numeric value to some textual name for the day, won't you?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[pankaj_wolfhunter]

thanks Knut. thanks all

[Serge Rielau]

pa***************@yahoo.co.in wrote:

thanks Knut. thanks all

Try this:

CREATE FUNCTION DAYOFWEEK_PANKAJ(dt DATE)
RETURNS SMALLINT
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN dayofweek_iso(dt - 1 day)

That should give you Tuesday as day one for this new function.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[singlal]

Hi,

I have a quesitons on the DAYOFWEEK function. I am currently using this
function and putting CASE statement to get english day name. The
function DAYNAME is not working. Is there any other function that can
be used to get the english name as string for, say current day?

Thanks in advance,
Lalit

[leonardobgil]

I am not sure how are you trying to use the dayname function.

It worked fine for me on 8.2

bash-2.05b$ db2 "select dayname(current date) from sysibm.sysdummy1"

1

----------------------------------------------------------------------------------------------------
Wednesday
1 record(s) selected.

[Serge Rielau]

singlal wrote:

Hi,

I have a quesitons on the DAYOFWEEK function. I am currently using this
function and putting CASE statement to get english day name. The
function DAYNAME is not working. Is there any other function that can
be used to get the english name as string for, say current day?

Thanks in advance,
Lalit

CREATE FUNCTION nameofday(dt DATE)
RETURNS VARCHAR(10)
CONTAINS SQL NO EXTERNALACTION DETERMINISTIC
RETURN
CASE dayofweek_iso(dt)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
WHEN 7 THEN 'Sunday'
END

That should do it (untested). Getting acquainted to SQL Functions is
very valuable.. Try it.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[singlal]

I tried SELECT DAYNAME(CURRENT DATE) FROM SYSIBM.SYSDUMMY1 but I am
getting SQLCODE -440. I am using DB2 v 7.1

[leonardobgil]

I guess you cannot run it on 7.1

Here is from one of the 7.2 databases I have..

bash-2.05a$ db2 "select dayname(current date) from sysibm.sysdummy1"

1
----------------------------------------------------------------------------------------------------
Thursday

1 record(s) selected.

bash-2.05a$ db2level
DB21085I Instance "testinst" uses DB2 code release "SQL07028" with
level
identifier "03090105" and informational tokens "DB2 v7.1.0.82",
"s030617" and
"U488490".

So looks like Serge's solution is the way to go for you... which also
sounds like what you already have been doing