 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / January 2006Regarding DAYOFWEEK function
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 > 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() :-)  SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab That was easy :-). How can we set at the instance level. i mean how is the exact implementation. > 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? SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab I am not getting. i mean how can i set it through CLP? > 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 SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab 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 > 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 [quoted text clipped - 3 lines] > > Is there a way? You can't do this at the instance level. Thanks Ian. Can we do it at database level? > 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? SignatureKnut Stolze DB2 Information Integration Development IBM Germany thanks Knut. thanks all > 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 SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab 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 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. > Hi, > [quoted text clipped - 5 lines] > 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 SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab I tried SELECT DAYNAME(CURRENT DATE) FROM SYSIBM.SYSDUMMY1 but I am getting SQLCODE -440. I am using DB2 v 7.1 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.