Home » SQL & PL/SQL » SQL & PL/SQL » how to exclude SATURDAY AND SUNDAY in a string of DATES (oracle 10g)
how to exclude SATURDAY AND SUNDAY in a string of DATES [message #432983] Sat, 28 November 2009 00:03 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
in one colum i will store the values like this.16-NOV-2009,17-NOV-2009,18-NOV-2009,19-NOV-2009,20-NOV-2009,21-NOV-2009,22-NOV-2009,23-NOV-2009;

i need get the final result opting out the saturday and sunday among these dates.so 21-NOV-2009,22-NOV-2009 are SATURDAY and SUNDAY.so finaly output should come as
16-NOV-2009,17-NOV-2009,18-NOV-2009,19-NOV-2009,20-NOV-2009,23-NOV-2009;


CREATE TABLE TEST
(
  ID         NUMBER NOT NULL PRIMARY KEY,
  REFERENCE     VARCHAR2(1000)
);
 
 INSERT INTO TEST(ID,REFERENCE) 
VALUES(1,16-NOV-2009,17-NOV-2009,18-NOV-2009,19-NOV-2009,20-NOV-2009,21-NOV-2009,22-NOV-2009,23-NOV-2009);

[Updated on: Sat, 28 November 2009 01:41] by Moderator

Report message to a moderator

Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #432984 is a reply to message #432983] Sat, 28 November 2009 00:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>INSERT INTO TEST(ID,REFERENCE) VALUES(1,16-NOV-2009,17-NOV-2009,18-NOV-2009,19-NOV-2009,20-NOV-2009,21-NOV-2009,22-NOV-2009,23-NOV-2009);

post complete session details.
Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #432987 is a reply to message #432983] Sat, 28 November 2009 01:40 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
SQL> select banner from v$version
2 /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #432988 is a reply to message #432983] Sat, 28 November 2009 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE TABLE TEST
  2  (
  3    ID         NUMBER NOT NULL PRIMARY KEY,
  4    REFERENCE     VARCHAR2(1000)
  5  );

Table created.

SQL>  
SQL>  INSERT INTO TEST(ID,REFERENCE) 
  2  VALUES(1,16-NOV-2009,17-NOV-2009,18-NOV-2009,
19-NOV-2009,20-NOV-2009,21-NOV-2009,22-NOV-2009,23-NOV-2009);
 INSERT INTO TEST(ID,REFERENCE)
             *
ERROR at line 1:
ORA-00913: too many values

When you post code, post something that compiles.

From where comes what you want to insert? What is the input? If string is static, what is your problem?

I think you should read about Normalization and do not post multiple values inside a single field.

Regards
Michel

[Updated on: Mon, 30 November 2009 05:21]

Report message to a moderator

Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #433072 is a reply to message #432983] Sun, 29 November 2009 13:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
The easy answer to your question is in the manuals.

Good luck, Kevin
Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #433119 is a reply to message #432983] Mon, 30 November 2009 04:16 Go to previous messageGo to next message
venkatesh.M.P
Messages: 37
Registered: February 2009
Location: india
Member
i searched but i am unable to find the solution
Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #433121 is a reply to message #433119] Mon, 30 November 2009 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 28 November 2009 08:45
...When you post code, post something that compiles.

From where comes what you want to insert? What is the input? If string is static, what is your problem?

I think you should read about Normalization and do not post multiple values inside a single field.?...


We are waiting for your answers.

Regards
Michel

Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #433127 is a reply to message #432983] Mon, 30 November 2009 05:19 Go to previous messageGo to next message
venkatesh.M.P
Messages: 37
Registered: February 2009
Location: india
Member
Firstly the forms&reports were in 4i&2.5i on windows98,oracle8i i was converted into forms&reports 6i on windowsxp,oracle9i when they were in lower version all was working well but when it comes here when i reun a report which was showing the total month dates in the old in new its not showing some dates in which the employee was absent for his/her duty without any intimation it was showing in the older version pls help me in this issue
Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #433128 is a reply to message #433127] Mon, 30 November 2009 05:22 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
So from the previous post, it would appear that you curently have a query to return results, it's just that it doesn't quite return the correct/expected results. Please post this query (properly formatted, with code tags). That'll give us all a starting point.
Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #433129 is a reply to message #433127] Mon, 30 November 2009 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand is this a Forms, a Reports or a SQL question?

Regards
Michel

[Updated on: Mon, 30 November 2009 05:23]

Report message to a moderator

Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #433134 is a reply to message #432983] Mon, 30 November 2009 05:37 Go to previous messageGo to next message
venkatesh.M.P
Messages: 37
Registered: February 2009
Location: india
Member
sir its a report sir i need the dates for the month to be diplayed with or without data (i.e., if i run the report from 01.09.09 to 30.09.09 it should show the dates in between them with or without data)
Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #433137 is a reply to message #433134] Mon, 30 November 2009 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already have an on-going topic (which is a merge of several ones) on this question, do not hijack others topic nor try to create new topics with the same question in other (sub-)forums.
In this topic you also got an answer, we are waiting you show us what you did (show in your topic not here).

Regards
Michel

[Updated on: Mon, 30 November 2009 05:52]

Report message to a moderator

Re: how to exclude SATURDAY AND SUNDAY in a string of DATES [message #433143 is a reply to message #433137] Mon, 30 November 2009 06:06 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
I didn't even realise that that was a hijack! (More coffee needed) I would vote for deleting the posts relating to the 'hijack'
Previous Topic: Left outer join with NVL and Where
Next Topic: Need To Be Displayed NULL COLUMNS (merged 3)
Goto Forum:
  


Current Time: Sun Dec 11 02:19:23 CST 2016

Total time taken to generate the page: 0.04623 seconds